Quiet Failures

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

mid

Asked in

Wells Fargo

Problem

A payments platform's release team is auditing how reliably each service ships, and the deploy log records a successful outcome inconsistently as both 'Success' and 'success'. For each service with at least 15 deployments this year, report its total deployments, how many succeeded, and the resulting success rate, least reliable first.

Some services land every release; others only look like they do. Find the ones production can't count on.

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 problem is really testing

Strip the costume and this is a per-service success rate riding on a dirty status column. Anyone can write a group-by with a ratio. The real test is whether you notice that 'Success' and 'success' are the same outcome written two ways, so a case-sensitive status = 'success' silently drops every capitalized win from your numerator. The query still runs and still prints tidy percentages, but every service that logged mixed casing now reads as far less reliable than it truly is. Get it wrong and gateway looks like a coin flip when it actually ships eleven of sixteen.

Break down the requirements

Step 1: Scope to this year's deployments

WHERE deploy_at >= '2026-01-01' limits the audit to the current year. Leave it out and you fold in four prior years of history, inflating every deploy_count and washing out the recent reliability picture the team asked for.

Step 2: Count successes case-insensitively

SUM(CASE WHEN LOWER(status) = 'success' THEN 1 ELSE 0 END) is the numerator. Compare status = 'success' instead and you skip every 'Success' row, so the count lands low and the rate droops below the truth. Normalize the casing inside the CASE, exactly once, and both the count and the rate come out right.

Step 3: Turn the counts into a rate and drop thin cohorts

100.0 * success_count / COUNT(*) forces float division so you do not floor the percentage to an integer 0. HAVING COUNT(*) >= 15 keeps a service with only a handful of deploys this year from landing at a noisy 0 or 100 percent and hijacking the ranking.

Step 4: List the least reliable first

ORDER BY success_pct ASC surfaces the shakiest services at the top. svc_name is the tie-break, so the four services sitting at 0.0 percent come out in a stable alphabetical order instead of whatever the engine happens to return.

The solution

SELECT
  svc_name,
  COUNT(*) AS deploy_count,
  SUM(CASE WHEN LOWER(status) = 'success' THEN 1 ELSE 0 END) AS success_count,
  ROUND(100.0 * SUM(CASE WHEN LOWER(status) = 'success' THEN 1 ELSE 0 END) / COUNT(*), 1) AS success_pct
FROM deploy_logs
WHERE deploy_at >= '2026-01-01'
GROUP BY svc_name
HAVING COUNT(*) >= 15
ORDER BY success_pct ASC, svc_name

Common follow-up questions

• The team now wants `rolled_back` deploys treated as outright failures and `in_progress` ones excluded entirely. Which part of the query changes, and does your denominator move? (Tests whether they separate the success predicate in the numerator from the population filter in the WHERE.)
• How would you keep this incremental over just the last 30 days on a 40M row table? (Probes partition pruning on `deploy_at` and why the LOWER(status) predicate must not block it.)
• A service shows 100 percent over 15 deploys while another shows 98 percent over 500. How would you rank so the small sample does not read as more reliable? (Pushes toward volume-weighting or a lower-bound confidence interval rather than a raw rate.)