Deployment Outcomes per Service

Easy
326.

Deployment Outcomes per Service

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

ColumnTypeValues
deploy_idint
servicevarchar
deployed_atdatetime
statusenumsucceeded, failed, rolled_back, in_progress

Primary key: deploy_id.

One row per deployment of a service to production.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Deployment

deploy_idservicedeployed_atstatus
1checkout-api2025-05-05 11:00:00succeeded
2checkout-api2025-05-06 15:30:00rolled_back
3checkout-api2025-05-07 10:10:00succeeded
4payments-svc2025-05-05 12:45:00failed
5payments-svc2025-05-07 18:20:00in_progress
6search-svc2025-05-06 09:00:00succeeded

Output

servicetotal_deployssucceededfailed
checkout-api321
payments-svc201
search-svc110
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.

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

ColumnType
deploy_idint
servicevarchar
deployed_atdatetime
statusenum(succeeded, failed, rolled_back, in_progress)

Primary key: deploy_id.

One row per deployment of a service to production.

Examples

Example 1

Deployment

deploy_idservicedeployed_atstatus
1checkout-api2025-05-05 11:00:00succeeded
2checkout-api2025-05-06 15:30:00rolled_back
3checkout-api2025-05-07 10:10:00succeeded
4payments-svc2025-05-05 12:45:00failed
5payments-svc2025-05-07 18:20:00in_progress
6search-svc2025-05-06 09:00:00succeeded

Output

servicetotal_deployssucceededfailed
checkout-api321
payments-svc201
search-svc110

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 service

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

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