Longest Streak of Clean Deploys per Service — SQL Problem
A service's deployments, taken in time order (by deployed_at, then deploy_id), form streaks of consecutive 'succeeded' deploys; any 'failed' or 'rolledback'…
- Difficulty: Hard
- Topics: Window Functions, Joins
- Dialect: MySQL
- Problem: #342
Problem statement
A service's deployments, taken in time order (by deployed_at, then deploy_id), form streaks of consecutive 'succeeded' deploys; any 'failed' or 'rolled_back' deploy ends a streak. The platform team celebrates the services with the longest clean streak.
For every service that has deployments, return the columns service, total_deploys and longest_streak — the length of its longest run of consecutive succeeded deploys, 0 if none ever succeeded — ordered by longest_streak descending, then service.
Tables
Table: ServiceDeploy
| Column | Type |
|---|---|
| deploy_id | int |
| service | varchar |
| deployed_at | datetime |
| status | enum(succeeded, failed, rolled_back) |
Primary key: deploy_id.
One row per finished production deploy. Two deploys of one service can share a timestamp (a re-run in the same second); deploy_id orders them.
Examples
Example 1
ServiceDeploy
| deploy_id | service | deployed_at | status |
|---|---|---|---|
| 1 | checkout-api | 2025-03-03 10:00:00 | succeeded |
| 2 | checkout-api | 2025-03-04 10:00:00 | succeeded |
| 3 | checkout-api | 2025-03-05 10:00:00 | rolled_back |
| 4 | checkout-api | 2025-03-06 10:00:00 | succeeded |
| 5 | checkout-api | 2025-03-07 10:00:00 | succeeded |
| 6 | checkout-api | 2025-03-08 10:00:00 | succeeded |
| 7 | payments-svc | 2025-03-03 12:00:00 | failed |
| 8 | payments-svc | 2025-03-04 12:00:00 | failed |
| 9 | search-svc | 2025-03-05 09:00:00 | succeeded |
| 10 | search-svc | 2025-03-06 09:00:00 | failed |
| 11 | search-svc | 2025-03-07 09:00:00 | succeeded |
| 12 | search-svc | 2025-03-07 09:00:00 | succeeded |
| 13 | search-svc | 2025-03-08 09:00:00 | succeeded |
Output
| service | total_deploys | longest_streak |
|---|---|---|
| checkout-api | 6 | 3 |
| search-svc | 5 | 3 |
| payments-svc | 2 | 0 |
How to solve Longest Streak of Clean Deploys per Service
Consecutive runs inside an ordered sequence are the gaps-and-islands pattern. Order each service's deploys by deployed_at and then deploy_id (two deploys can share a second, and the id fixes their order). Number every deploy in that order (rn_all) and, separately, number it among the deploys of the same service with the same status (rn_status). Inside a run of consecutive succeeded deploys both counters grow by one per row, so rn_all - rn_status is constant; a failure in between advances rn_all but not the succeeded counter, so the next run gets a different key. Grouping the succeeded rows by (service, key) yields one row per streak with its length.
A service whose every deploy failed has no streak rows at all. Starting from the per-service totals and LEFT JOINing the streaks keeps it, and COALESCE(MAX(len), 0) turns its missing maximum into 0.
The alternative labels streaks with a running count of failures: SUM(non-success) OVER (… ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) stays constant across a run of successes and steps up at each failure. Each group then holds at most one failure followed by a streak, so counting its succeeded rows gives the streak length, a group of only failures gives 0, and summing the group sizes recovers the total — no join needed. The explicit ROWS frame matters: with ties in the ORDER BY, the default RANGE frame would include peers. Both approaches sort each service once.
Reference solution (MySQL)
WITH ordered AS (
SELECT service, status,
ROW_NUMBER() OVER (PARTITION BY service ORDER BY deployed_at, deploy_id) AS rn_all,
ROW_NUMBER() OVER (PARTITION BY service, status ORDER BY deployed_at, deploy_id) AS rn_status
FROM ServiceDeploy
), streaks AS (
SELECT service, rn_all - rn_status AS island, COUNT(*) AS len
FROM ordered
WHERE status = 'succeeded'
GROUP BY service, rn_all - rn_status
), totals AS (
SELECT service, COUNT(*) AS total_deploys FROM ServiceDeploy GROUP BY service
)
SELECT t.service, t.total_deploys, COALESCE(MAX(s.len), 0) AS longest_streak
FROM totals t
LEFT JOIN streaks s ON s.service = t.service
GROUP BY t.service, t.total_deploys
ORDER BY longest_streak DESC, t.serviceAnother way
WITH marked AS (
SELECT service, status,
SUM(CASE WHEN status = 'succeeded' THEN 0 ELSE 1 END)
OVER (PARTITION BY service ORDER BY deployed_at, deploy_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS breaks
FROM ServiceDeploy
), runs AS (
SELECT service, breaks, SUM(CASE WHEN status = 'succeeded' THEN 1 ELSE 0 END) AS len, COUNT(*) AS rows_in_group
FROM marked GROUP BY service, breaks
)
SELECT service, SUM(rows_in_group) AS total_deploys, MAX(len) AS longest_streak
FROM runs
GROUP BY service
ORDER BY longest_streak DESC, service← 90th Percentile API Latency per Endpoint · Total Outage Minutes per Service With Overlaps Merged →