Deploy Outcomes by Service

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

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.

Success, failure, rollback - side by side.

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

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.

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.

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))

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.

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

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