# Deploy Outcomes by Service

> Success, failure, rollback - side by side.

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

Domain: SQL · Difficulty: hard · Seniority: L4 · Asked in: City and County of San Francisco

## Problem

Nobody standardized `deploy_logs`: one service shows up in `svc_name` under different casings and with stray spaces, any `status` that mentions success counts as a success, and 'failed' and 'rolled_back' count whatever their casing. An on-call engineer wants a scorecard with one row per service and its successful, failed and rolled-back deploy counts side by side.

## Tables

### deploy_logs

- log_id INTEGER
- svc_name TEXT
- version TEXT
- env_name TEXT
- status TEXT
- deploy_at TEXT
- dur_secs INTEGER
- author TEXT

Sample rows (10 of 200):

| log_id | svc_name | version | env_name | status | deploy_at | dur_secs | author |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 131 | payment-api | v1.0.2 | staging | failed | 2026-02-02 01:11:00 | 23 | bob |
| 162 | user-svc | v1.1.0 | dev | rolled_back | 2026-03-03 02:22:00 | 36 | charlie |
| 193 | search-api | 2.0.0 | canary | in_progress | 2026-04-04 03:33:00 | 49 | dana |
| 224 | gateway | v2.1.0 | Production | Success | 2026-05-05 04:44:00 | 62 | eve |
| 255 | notif-svc | 2.1.1 | STAGING | FAILED | 2026-06-06 05:55:00 | 75 | frank |
| 286 | analytics | v3.0.0-rc1 | production | success | 2026-07-07 06:06:00 | 88 | Alice |
| 317 | ml-serving | v1.0.3 | staging | failed | 2026-08-08 07:17:00 | NULL | BOB |
| 348 | auth-svc | v1.0.1 | dev | Rolled_Back | 2026-09-09 08:28:00 | 114 | alice |
| 379 | Payment-API | v1.0.2 | canary | in_progress | 2026-01-10 09:39:00 | 127 | bob |
| 410 | user-svc | v1.1.0 | Production | Success | 2026-02-11 10:50:00 | 140 | charlie |

## Expected output

First 5 of 8 rows:

| svc_name | success_count | failed_count | rolled_back_count |
| --- | --- | --- | --- |
| analytics | 16 | 0 | 8 |
| auth-svc | 16 | 0 | 8 |
| gateway | 18 | 0 | 8 |
| ml-serving | 0 | 16 | 0 |
| notif-svc | 0 | 16 | 0 |

## Worked solution and explanation

### A pivot hiding behind two dirty columns

This is a pivot dressed up as an on-call scorecard. A long `status` column has to become three fixed outcome columns with one row per service, and the catch is that neither column you touch was ever cleaned. Anyone can write three `SUM(CASE ...)` expressions. What separates candidates is normalizing before comparing: test the raw `status = 'failed'` and every 'FAILED' row counts as zero, so failures are undercounted. Group on the raw `svc_name` and 'payment-api' comes back as four rows, so the shape is wrong too.

> **Normalize the key you group on, and every label you test**
>
> Group on `LOWER(TRIM(svc_name))` and wrap `status` in `LOWER(...)` inside each branch. After that every outcome is a plain `SUM(CASE WHEN ... THEN 1 ELSE 0 END)` over clean values, all in one pass.

---

### Build it in three moves

#### Step 1: Clean the grouping key

"One row per service" is only true if one service has one spelling. The seed stores ' payment-api', 'payment-api ', 'Payment-API' and 'payment-api'. Select and group by the same `LOWER(TRIM(svc_name))` expression; skip either function and a padded or capitalised copy survives as its own row.

#### Step 2: Lower `status` inside every branch

Each `CASE` compares `LOWER(status)`, so 'FAILED' and '`Rolled_Back`' land in their columns instead of vanishing. Lowering only the branch you expect to be dirty is how a rollback quietly goes missing.

#### Step 3: Pivot with three conditional sums

Success uses `LIKE '%success%'` because any label mentioning success counts; failed and rolled-back are exact matches on the lowered label. A row like '`in_progress`' matches no branch and adds 0 to all three.

**Normalize, then pivot**

```sql
SELECT
  LOWER(TRIM(svc_name)) AS svc_name,
  SUM(CASE WHEN LOWER(status) LIKE '%success%' THEN 1 ELSE 0 END) AS success_count,
  SUM(CASE WHEN LOWER(status) = 'failed' THEN 1 ELSE 0 END) AS failed_count,
  SUM(CASE WHEN LOWER(status) = 'rolled_back' THEN 1 ELSE 0 END) AS rolled_back_count
FROM deploy_logs
GROUP BY LOWER(TRIM(svc_name))
```

*One grouped pass over `deploy_logs`, cleaning the key and the label inline.*

**Long format**

`GROUP BY svc_name, status` returns one row per raw pair. Every spelling of every service and every outcome is its own row, so the reader has to reassemble the scorecard by hand.

**Pivoted**

`GROUP BY` the cleaned `svc_name` with three conditional sums returns exactly one row per service and three fixed columns, whatever spellings the data holds.

**The long-format trap**

```sql
SELECT svc_name, status, COUNT(*) AS deploys
FROM deploy_logs
GROUP BY svc_name, status
```

*Raw keys, raw labels: fragmented rows and split counts.*

> **Lowering `status` is not enough**
>
> Most candidates who catch the mixed-case `status` still group on the raw `svc_name`. The counts inside each row look right, but 'payment-api' appears four times and none of those rows holds its real total.

> **Profile the key before you trust it**
>
> Before writing the pivot, a senior candidate asks what values `svc_name` and `status` actually hold. Spotting the padded service name unprompted is the tell that you have shipped reports off raw logs before.

> **One scan, about 120 groups**
>
> At 1.5M rows the plan reads `deploy_logs` once and keeps a tiny hash table of roughly 120 services with three counters each. The per-row `LOWER` and `TRIM` calls are cheap string work; three filtered `COUNT(*)` queries joined back together would scan the table three times.

---

## Common follow-up questions

- A new label such as 'canceled' starts appearing in `status`. What happens to your result? _(Tests whether the candidate sees that fixed `CASE` branches silently drop new categories, and whether they would add an 'other' column.)_
- Leadership wants a success rate per service next to the counts. How do you add it without a second pass? _(Tests reuse of the conditional sums and guarding a zero denominator.)_
- If `svc_name` dirt keeps arriving, where should the cleaning live? _(Tests whether the candidate pushes normalization upstream into ingestion instead of repeating it in every query.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/deploy_outcomes_by_service)
- [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.