# Blast Radius

> When deploys fail, how bad is the blast radius?

Canonical URL: <https://datadriven.io/problems/blast_radius>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

After a string of rollbacks, leadership asked how much deployment capacity each service is losing to failures. For every service, show the share of its deployments that failed and the share of its total deployment time those failures consumed.

## 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 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. The quieter trap is the data itself: 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. Forget NULLIF and ml-serving, whose only deploy has a null duration, divides by zero.

---

### Break down the requirements

#### Step 1: Group by service

For each svc_name, count deployments whose status is a failure: case-insensitively 'failed' or the synonym 'rolled_back' (the prose's 'string of rollbacks'); 'in_progress' and 'success' are not failures but still count toward the totals.

#### Step 2: Compute both percentages in one pass

Failure share = 100.0 * failed-deployment count / total deployment count, expressed as a conditional SUM over COUNT(*). The duration share swaps the counted 1 for dur_secs in the same conditional SUM, over SUM(dur_secs).

---

### The solution

**Dual failure percentage computation**

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

> **Cost Analysis**
>
> Single scan of 1.5M rows. Both percentages come out of one aggregation pass, so there is no self-join and no second query. NULLIF turns an all-null duration total into a null result instead of a division-by-zero crash.

> **Interviewers Watch For**
>
> Whether the candidate lands both percentages in a single conditional-aggregation pass, and whether they fold case and treat rolled_back as a failure, rather than matching the literal 'failed'.

> **Common Pitfall**
>
> Comparing status against 'failed' literally under-counts every service that logged 'FAILED' or 'rolled_back'. Fold the case and include the synonym. Separately, SUM(dur_secs) in the denominator without NULLIF risks division by zero for a service whose durations are all null.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/blast_radius)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.