Deployment Outcomes per Service — SQL Problem
The release dashboard shows, for every service, how its deployments went.
- Difficulty: Easy
- Topics: Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #326
Problem statement
The release dashboard shows, for every service, how its deployments went. A deployment with status 'succeeded' went out cleanly; 'failed' and 'rolled_back' both count as failed; a deployment still 'in_progress' counts toward the total but is neither succeeded nor failed.
Return one row per service that has deployments, with the columns service, total_deploys, succeeded and failed. Return the rows in any order.
Tables
Table: Deployment
| Column | Type |
|---|---|
| deploy_id | int |
| service | varchar |
| deployed_at | datetime |
| status | enum(succeeded, failed, rolled_back, in_progress) |
Primary key: deploy_id.
One row per deployment of a service to production.
Examples
Example 1
Deployment
| deploy_id | service | deployed_at | status |
|---|---|---|---|
| 1 | checkout-api | 2025-05-05 11:00:00 | succeeded |
| 2 | checkout-api | 2025-05-06 15:30:00 | rolled_back |
| 3 | checkout-api | 2025-05-07 10:10:00 | succeeded |
| 4 | payments-svc | 2025-05-05 12:45:00 | failed |
| 5 | payments-svc | 2025-05-07 18:20:00 | in_progress |
| 6 | search-svc | 2025-05-06 09:00:00 | succeeded |
Output
| service | total_deploys | succeeded | failed |
|---|---|---|---|
| checkout-api | 3 | 2 | 1 |
| payments-svc | 2 | 0 | 1 |
| search-svc | 1 | 1 | 0 |
How to solve Deployment Outcomes per Service
This is conditional aggregation: one GROUP BY, several counts that each look at a different subset of the group's rows. COUNT(*) is the total. For the others, a CASE turns each row into 1 when it belongs to the bucket and 0 when it does not, and SUM adds them up — SUM(CASE WHEN status = 'succeeded' THEN 1 ELSE 0 END).
The failed bucket takes two statuses, so its CASE tests status IN ('failed', 'rolled_back'). A deployment that is still in progress is in the total but in neither bucket, which is why succeeded + failed can be less than total_deploys — a good check that the buckets were defined as the statement says.
COUNT(CASE WHEN … THEN 1 END) works the same way, because a CASE without ELSE yields NULL and COUNT skips NULLs; SUM(IF(…)) is MySQL's shorthand. Every form reads the table once.
Reference solution (MySQL)
SELECT service,
COUNT(*) AS total_deploys,
SUM(CASE WHEN status = 'succeeded' THEN 1 ELSE 0 END) AS succeeded,
SUM(CASE WHEN status IN ('failed', 'rolled_back') THEN 1 ELSE 0 END) AS failed
FROM Deployment
GROUP BY serviceAnother way
SELECT service, COUNT(*) AS total_deploys,
SUM(IF(status = 'succeeded', 1, 0)) AS succeeded,
COUNT(*) - SUM(IF(status = 'succeeded', 1, 0)) - SUM(IF(status = 'in_progress', 1, 0)) AS failed
FROM Deployment GROUP BY serviceAnother way
SELECT service, COUNT(*) AS total_deploys,
COUNT(CASE WHEN status = 'succeeded' THEN 1 END) AS succeeded,
COUNT(CASE WHEN status <> 'succeeded' AND status <> 'in_progress' THEN 1 END) AS failed
FROM Deployment GROUP BY service← Hosts That Hit CPU Saturation · Error Codes in the API Gateway Logs →