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
| Column | Type |
|---|---|
| deploy_id | int |
| service | varchar |
| deployed_at | datetime |
| caused_incident | bool |
| rolled_back | bool |
Primary key: deploy_id.
One row per production deployment. The two flags are 1 or 0.
Examples
Example 1
ProdDeploy
| deploy_id | service | deployed_at | caused_incident | rolled_back |
|---|---|---|---|---|
| 1 | checkout-api | 2025-01-08 11:00:00 | 0 | 0 |
| 2 | payments-svc | 2025-01-15 16:20:00 | 1 | 1 |
| 3 | search-svc | 2025-01-31 23:50:00 | 0 | 1 |
| 4 | auth-svc | 2025-02-01 00:10:00 | 0 | 0 |
| 5 | checkout-api | 2025-02-12 10:30:00 | 1 | 0 |
| 6 | catalog-api | 2025-02-20 14:45:00 | 0 | 0 |
| 7 | notify-worker | 2025-02-27 09:15:00 | 0 | 0 |
| 8 | payments-svc | 2025-04-03 12:00:00 | 0 | 0 |
Output
| month | deployments | failed_changes | change_failure_pct |
|---|---|---|---|
| 2025-01 | 3 | 2 | 66.67 |
| 2025-02 | 4 | 1 | 25 |
| 2025-04 | 1 | 0 | 0 |
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 monthAnother 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 →