# Quiet Failures > Some services land every release; others only look like they do. Find the ones production can't count on. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: mid ## 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. ## 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 **This year's deployment success rate** ```sql 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 ``` > **One scan, one normalization** > > The whole query is a single pass. The CASE normalizes status casing for the numerator while COUNT(*) supplies the denominator, no self-join and no second query. Once you see that the numerator and denominator differ only by a case-insensitive predicate, the query writes itself. > **Cost Analysis** > > At 40M deploy rows, LOWER(status) inside the aggregate is cheap because it runs per surviving row, but a case-sensitive index on status buys you nothing here. In production you either store a normalized status at ingest or add a functional index on LOWER(status); bound the scan with the deploy_at predicate so the planner reads only this year's partitions before grouping. > **Common Pitfall** > > Two silent killers stack here. status = 'success' quietly excludes 'Success' rows, understating every numerator, and integer division floors 16/24 to 0 unless you multiply by 100.0. Both produce a result that looks plausible in review, so the wrong numbers ship. Normalize the categorical and force float division before you trust a rate. > **Interviewers Watch For** > > Whether you reach for LOWER() without being told the status column is dirty, whether you compute the rate with one conditional aggregation instead of two separate queries divided afterward, and whether you add the HAVING guard so a three-deploy service does not top a least-reliable list on no real evidence. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/quiet_failures) - [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.