Blast Radius

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

IBM

Problem

After a run of rollbacks, leadership wants to know how much deployment capacity each service is bleeding to failures. For every service, report the share of its finished deployments that failed, and the share of all the time it spent deploying, including deploys still in flight, that those failures consumed. List the services alphabetically.

When deploys fail, how bad is the blast radius?

Tables

deploy_logs

Columns: log_id INTEGER, svc_name TEXT, version TEXT, env_name TEXT, status TEXT, deploy_at TEXT, dur_secs INTEGER, author TEXT

Expected output

Worked solution and explanation

What this really is

Strip the incident-review costume and this is two conditional percentages hiding in one table: what share of a service's finished deploys failed, and what share of its deploy-seconds those failures burned. Anyone can filter for failures. The tell is spotting that both numbers are conditional SUMs over the same GROUP BY, so the whole thing collapses into a single scan instead of two queries. Two quieter traps live in the data. First, status arrives as 'FAILED', 'failed', and 'Success' all at once, and 'rolled_back' counts as a failure even though the word 'failed' never appears in it; miss the case-fold or the synonym and your failure counts silently under-report. Second, the two percentages do not share a denominator. The failure RATE is about outcomes, so it runs over finished deploys only; an in_progress deploy has no outcome yet and would dilute the rate if you left it in. The TIME share is about consumed capacity, and an in_progress deploy is still burning deploy-seconds right now, so its elapsed time stays in the time denominator. That split is exactly why you cannot just filter in_progress out with a WHERE: you need it gone from the count but present in the time total.

Break down the requirements

Step 1: Classify each deploy for one service at a time

For each svc_name, a deployment is a failure when LOWER(status) is 'failed' or the synonym 'rolled_back' (the prose's run of rollbacks). A deploy still in_progress has no outcome yet, so it is neither a failure nor part of the finished-deploy count, but it has still consumed deployment time.

Step 2: Compute both percentages in one pass

Count share = 100.0 * failed-deploy count / count of FINISHED deploys. COUNT(NULLIF(LOWER(status), 'in_progress')) turns each in_progress row into NULL, and COUNT skips NULLs, so only completed deploys land in that denominator. The time share swaps the counted 1 for dur_secs in the numerator, over SUM(dur_secs) across every row in the group, in_progress included, because those deploys are still consuming capacity so their elapsed time belongs in the total. That is why you reach for NULLIF instead of a WHERE filter: a WHERE that dropped in_progress would also strip its time from the denominator and inflate every failure-time share.

The solution

SELECT
    svc_name,
    ROUND(100.0 * SUM(CASE WHEN LOWER(status) IN ('failed', 'rolled_back') THEN 1 ELSE 0 END) / COUNT(NULLIF(LOWER(status), 'in_progress')), 2) AS failure_pct,
    ROUND(100.0 * SUM(CASE WHEN LOWER(status) IN ('failed', 'rolled_back') THEN dur_secs ELSE 0 END) / SUM(dur_secs), 2) AS failure_time_pct
FROM deploy_logs
GROUP BY svc_name
ORDER BY svc_name

Common follow-up questions

• What if a service has zero deployments in the window? (It would not appear in the GROUP BY output. Tests whether to LEFT JOIN a service dimension table to surface zero-deploy services.)
• How would you surface the service whose failures burned the most deployment time? (ORDER BY `failure_time_pct` DESC LIMIT 1, with a tie-break decision to raise.)
• What other failure metrics would round out this dashboard? (MTTR (mean time to recovery), failure frequency over time, failure share by environment, etc.)