Rollback Roulette

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Virgin Group

Problem

Count how many deployments in deploy_logs ended in a failed status, across every service and environment the log covers. Several CI tools have written to that log over the years, so a failure counts however the tool happened to spell its status.

Some ships sink before they leave the harbor.

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 is really asking

This is a data-cleaning question wearing an incident-review costume. Counting rows behind one predicate is trivial; the skill is noticing that status is not one vocabulary but several, written by different CI tools, and that 'failed' and 'FAILED' are the same outcome. Anyone writes COUNT(*) with a WHERE. What separates candidates is folding case on the column before comparing. Skip it and the count comes back short by exactly the failures one tool chose to shout.

Building it

Step 1: Fold status to one case

Wrap the column, not the value: LOWER(status) maps 'FAILED', 'Failed' and 'failed' onto one string, so a single comparison catches every tool's spelling, including one nobody has written yet.

Step 2: Match only the failed outcome

Compare the folded value to 'failed' and nothing else. 'rolled_back' and 'in_progress' are different outcomes; a rollback means the failure was handled, and an in-flight deploy has not ended at all.

Step 3: Count the rows that survive

COUNT(*) over the surviving rows, aliased to failed_deployments. Every row is one deploy and log_id is unique, so there is nothing to deduplicate.

SELECT COUNT(*) AS failed_deployments
FROM deploy_logs
WHERE LOWER(status) = 'failed'

Exact match

WHERE status = 'failed' returns a believable number and never errors. It also skips every row a tool logged as 'FAILED', so the total is quietly low.

Folded match

WHERE LOWER(status) = 'failed' treats every casing as the same outcome, so the count reflects what actually happened rather than which tool happened to log it.

Common follow-up questions

Common follow-up questions

• What share of all deploys failed? (Conditional aggregation over the whole table, still folding `status` before comparing.)
• Which service has the most failed deploys? (`svc_name` has its own casing and padding dirt, so the grouping key needs cleaning too.)
• How would you split failures by `env_name`? (`env_name` mixes 'production' and 'Production', so the same folding applies to the grouping column.)