Longest Streak of Clean Deploys per Service

Hard
342.

Longest Streak of Clean Deploys per Service

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

ColumnTypeValues
deploy_idint
servicevarchar
deployed_atdatetime
statusenumsucceeded, 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.

Example

The rows must come back in the order shown.

ServiceDeploy

deploy_idservicedeployed_atstatus
1checkout-api2025-03-03 10:00:00succeeded
2checkout-api2025-03-04 10:00:00succeeded
3checkout-api2025-03-05 10:00:00rolled_back
4checkout-api2025-03-06 10:00:00succeeded
5checkout-api2025-03-07 10:00:00succeeded
6checkout-api2025-03-08 10:00:00succeeded
7payments-svc2025-03-03 12:00:00failed
8payments-svc2025-03-04 12:00:00failed
9search-svc2025-03-05 09:00:00succeeded
10search-svc2025-03-06 09:00:00failed
3 more rows

Output

servicetotal_deployslongest_streak
checkout-api63
search-svc53
payments-svc20
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
deploy_idint
servicevarchar
deployed_atdatetime
statusenum(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_idservicedeployed_atstatus
1checkout-api2025-03-03 10:00:00succeeded
2checkout-api2025-03-04 10:00:00succeeded
3checkout-api2025-03-05 10:00:00rolled_back
4checkout-api2025-03-06 10:00:00succeeded
5checkout-api2025-03-07 10:00:00succeeded
6checkout-api2025-03-08 10:00:00succeeded
7payments-svc2025-03-03 12:00:00failed
8payments-svc2025-03-04 12:00:00failed
9search-svc2025-03-05 09:00:00succeeded
10search-svc2025-03-06 09:00:00failed
11search-svc2025-03-07 09:00:00succeeded
12search-svc2025-03-07 09:00:00succeeded
13search-svc2025-03-08 09:00:00succeeded

Output

servicetotal_deployslongest_streak
checkout-api63
search-svc53
payments-svc20

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.service

Another 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 →