Monthly Change Failure Rate of Production Deploys

Medium
334.

Monthly Change Failure Rate of Production Deploys

The change failure rate is one of the four DORA metrics: the share of production deployments that caused an incident or had to be rolled back (either flag is enough; a deploy with both is still one failed change).

For every calendar month that has deployments, return the columns month (formatted 'YYYY-MM'), deployments, failed_changes and change_failure_pct — 100 * failed_changes / deployments rounded to 2 decimals — ordered by month.

Tables

Table: ProdDeploy

ColumnType
deploy_idint
servicevarchar
deployed_atdatetime
caused_incidentbool
rolled_backbool

Primary key: deploy_id.

One row per production deployment. The two flags are 1 or 0.

Example

The rows must come back in the order shown.

ProdDeploy

deploy_idservicedeployed_atcaused_incidentrolled_back
1checkout-api2025-01-08 11:00:0000
2payments-svc2025-01-15 16:20:0011
3search-svc2025-01-31 23:50:0001
4auth-svc2025-02-01 00:10:0000
5checkout-api2025-02-12 10:30:0010
6catalog-api2025-02-20 14:45:0000
7notify-worker2025-02-27 09:15:0000
8payments-svc2025-04-03 12:00:0000

Output

monthdeploymentsfailed_changeschange_failure_pct
2025-013266.67
2025-024125
2025-04100
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.

Monthly Change Failure Rate of Production Deploys

The change failure rate is one of the four DORA metrics: the share of production deployments that caused an incident or had to be rolled back (either flag…

  • Difficulty: Medium
  • Topics: Dates, Conditional Logic, Aggregation
  • Dialect: MySQL
  • Problem: #334

Problem statement

The change failure rate is one of the four DORA metrics: the share of production deployments that caused an incident or had to be rolled back (either flag is enough; a deploy with both is still one failed change).

For every calendar month that has deployments, return the columns month (formatted 'YYYY-MM'), deployments, failed_changes and change_failure_pct — 100 * failed_changes / deployments rounded to 2 decimals — ordered by month.

Tables

Table: ProdDeploy

ColumnType
deploy_idint
servicevarchar
deployed_atdatetime
caused_incidentbool
rolled_backbool

Primary key: deploy_id.

One row per production deployment. The two flags are 1 or 0.

Examples

Example 1

ProdDeploy

deploy_idservicedeployed_atcaused_incidentrolled_back
1checkout-api2025-01-08 11:00:0000
2payments-svc2025-01-15 16:20:0011
3search-svc2025-01-31 23:50:0001
4auth-svc2025-02-01 00:10:0000
5checkout-api2025-02-12 10:30:0010
6catalog-api2025-02-20 14:45:0000
7notify-worker2025-02-27 09:15:0000
8payments-svc2025-04-03 12:00:0000

Output

monthdeploymentsfailed_changeschange_failure_pct
2025-013266.67
2025-024125
2025-04100

How to solve Monthly Change Failure Rate of Production Deploys

Date bucketing plus conditional aggregation. DATE_FORMAT(deployed_at, '%Y-%m') turns every timestamp into its month key, '2025-01'; grouping by that expression puts each deployment in its month, and because the key is zero-padded year-then-month it also sorts chronologically as text, so ORDER BY month is correct.

Inside a month, COUNT(*) is the number of deployments and a CASE that is 1 when caused_incident = 1 OR rolled_back = 1 marks the failed changes. Adding the two flags would count a deploy that both caused an incident and was rolled back twice; the OR (or GREATEST of the two flags, as in the alternative) counts it once. The rate is 100 * failed / deployments, rounded to 2 decimals so the four-decimal division of MySQL and the floating division of other engines print the same number.

Months with no deployment have no rows and therefore no group — they are simply absent, which the statement allows. The alternative builds the key with CONCAT, YEAR and LPAD(MONTH(…), 2, '0'); without the padding '2025-10' would sort before '2025-9'. One scan and a small sort either way.

Reference solution (MySQL)

SELECT DATE_FORMAT(deployed_at, '%Y-%m') AS month,
       COUNT(*) AS deployments,
       SUM(CASE WHEN caused_incident = 1 OR rolled_back = 1 THEN 1 ELSE 0 END) AS failed_changes,
       ROUND(100 * SUM(CASE WHEN caused_incident = 1 OR rolled_back = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS change_failure_pct
FROM ProdDeploy
GROUP BY DATE_FORMAT(deployed_at, '%Y-%m')
ORDER BY month

Another way

WITH d AS (
  SELECT CONCAT(YEAR(deployed_at), '-', LPAD(MONTH(deployed_at), 2, '0')) AS month,
         GREATEST(caused_incident, rolled_back) AS failed
  FROM ProdDeploy
)
SELECT month, COUNT(*) AS deployments, SUM(failed) AS failed_changes,
       ROUND(100 * SUM(failed) / COUNT(*), 2) AS change_failure_pct
FROM d GROUP BY month ORDER BY month

← Slowest Endpoint in Each Service · Agents Carrying More Than Their Team's Average Workload →