# 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 to production, where the deploy log records that environment inconsistently as both 'Production' and 'production'. For each service with at least three production deployments, report its total deployments, how many carried a 'success' status, and the resulting success rate, least reliable first. ## Worked solution and explanation ### What this problem is really testing This is a conditional success rate per group, and the disguise is 'deployment reliability'. Anyone can write GROUP BY svc_name with a ratio. The skill being probed is whether you notice that both the filter and the numerator ride on a categorical column that is dirtily cased: env_name arrives as both 'Production' and 'production', and status as both 'Success' and 'success'. Compare with plain equality and half your rows silently vanish from the denominator and the numerator at once. The query still runs, still returns tidy percentages, and every one of them is wrong. A service that ships fine can look broken, or a shaky one can look clean, and nothing errors to tell you. --- ### Break down the requirements #### Step 1: Scope to production, case-insensitively LOWER(env_name) = 'production' is the denominator. Writing env_name = 'production' drops every 'Production' row, so a service's deploy_count is understated and its rate is computed off a smaller, biased sample. Normalize in the WHERE, not after. #### Step 2: Count successes with the SAME normalization SUM(CASE WHEN LOWER(status) = 'success' THEN 1 ELSE 0 END) counts the wins. If you compare status = 'success' you skip every 'Success' row, which understates the numerator only, so the rate droops below the truth. The fix has to be applied to status exactly the way it was applied to env_name. #### Step 3: Turn the two counts into a rate and drop thin services 100.0 * success_count / COUNT(*) gives the percentage; the 100.0 forces float division so you do not get an integer 0. HAVING COUNT(*) >= 3 keeps a service with a single deploy from landing at 0 or 100 percent and dominating the ordering on no real evidence. #### Step 4: Order least reliable first ORDER BY success_pct ASC surfaces the shakiest services at the top, with svc_name as a stable tie-break so equal rates come out in a deterministic order. --- ### The solution **Production 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 LOWER(env_name) = 'production' GROUP BY svc_name HAVING COUNT(*) >= 3 ORDER BY success_pct ASC, svc_name ``` > **One pass, two normalizations** > > The elegant move is doing everything in a single scan: the WHERE normalizes env_name for the denominator, and the CASE inside the SUM normalizes status for the numerator. No self-join, no second query. Once you see that both filters key off the same dirty-casing problem, the whole query writes itself. > **Cost Analysis** > > At 40M deploy rows partitioned by deploy_at, LOWER(env_name) in the WHERE defeats a plain index on env_name, forcing a scan of every partition unless you also bound deploy_at. In production you either store a normalized env column at ingest or build a functional index on LOWER(env_name); either lets the planner prune to the handful of 'production' rows before the group-and-aggregate. > **Common Pitfall** > > Case-sensitive comparison is the killer here: env_name = 'production' AND status = 'success' quietly excludes 'Production' and 'Success' rows, and because it excludes them from BOTH counts unevenly, the resulting percentage looks plausible in review. The bug ships. Always normalize a categorical before you filter or bucket on it. > **Interviewers Watch For** > > Whether you reach for LOWER() without being told the data is dirty, whether you compute the rate with conditional aggregation in one pass instead of two separate COUNT queries divided afterward, and whether you add the HAVING guard so a one-deploy service does not top a 'least reliable' list. --- ## Common follow-up questions - The team now wants rolled_back deployments counted as failures too. Where does that logic go, and does it change your denominator? _(Tests whether they see that only the numerator's success predicate changes; the denominator is still all production deploys.)_ - How would you make this incremental over just the last 30 days of deploys on a 40M row table? _(Probes partition pruning on deploy_at and why the LOWER() predicate must not block it.)_ - A service has 100 percent success but only three deploys. How would you express confidence so it does not read as more reliable than one with 98 percent over 500 deploys? _(Pushes toward volume-weighting or a lower-bound interval rather than a raw rate.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/quiet-failures-production-deploy-reliability) - [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.