# The Noise Floor > Not every alert deserves a 3 a.m. page. Find the services where most of them claim they do. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: mid ## Problem A reliability team is separating the services that page on genuine emergencies from the ones that mostly cry wolf, where a real emergency means a critical-severity alert. For each service, report the share of its alerts that are high or critical, keep only the services where critical alerts are more than 80 percent of that high-or-critical volume, and list the loudest services first. ## Worked solution and explanation ### What this is really asking Strip the noise-floor costume off and this is a conditional share with the ratio scoped to a subset of the rows. The skill being probed: can you express 'what fraction of a service's paging alerts are actually critical' when the paging alerts are themselves a filtered count. Anyone can divide criticals by total alerts. The trick is that the denominator has to be the high-or-critical count, not COUNT(*), and the bar has to sit above the critical-of-all ratio so only the scoped denominator clears it. Divide by every alert instead and a service that never fires a real critical still reads as an emergency, while a genuinely critical-heavy service can slip under the line. --- ### The trap lives in the denominator **Criticals over all alerts** SUM(critical) / COUNT(*) tops out near 0.67 on this data because roughly a third of every service's alerts are medium or low. An 80 percent bar against this denominator returns nothing, and a 50 percent bar lets a service through on its overall critical volume rather than on how it pages. **Criticals over the paging alerts** SUM(critical) / SUM(high or critical) is 1.0 for a service whose pages are all critical and 0.0 for one that only ever cries 'high'. That is the ratio that actually separates the two families, and an 80 percent bar cleanly isolates the critical-dominated services. ### Building it #### Step 1: Normalize case first severity arrives as 'Critical', 'HIGH', 'critical', and 'high'. Wrap it in LOWER() before every comparison, or a case-sensitive match undercounts and both the numerator and the denominator drift. #### Step 2: Report the paging share urgent_pct is the share of all of a service's alerts that page someone: SUM(CASE WHEN LOWER(severity) IN ('critical','high') THEN 1 ELSE 0 END) over COUNT(*). Multiply by 100.0 so the division is floating point, then round to one decimal. #### Step 3: Filter on critical dominance, scoped to the pages The keep-condition is a ratio of two conditional counts: criticals over the high-or-critical count. A service qualifies only when that exceeds 0.8. The denominator is NOT COUNT(*): dividing criticals by every alert tops out near 0.67 here, so an 80 percent bar against COUNT(*) would return nothing. The scoped denominator is the whole point. #### Step 4: Order for the on-call reader ORDER BY urgent_pct descending puts the loudest services on top, with svc_name as the tiebreak so two services on the same share come back in a stable order, exactly as the preview shows. --- ### The solution **CRITICAL-DOMINATED SERVICES WITH THEIR PAGING SHARE** ```sql SELECT svc_name, ROUND(100.0 * SUM(CASE WHEN LOWER(severity) IN ('critical', 'high') THEN 1 ELSE 0 END) / COUNT(*), 1) AS urgent_pct FROM alert_events GROUP BY svc_name HAVING SUM(CASE WHEN LOWER(severity) = 'critical' THEN 1 ELSE 0 END) * 1.0 / SUM(CASE WHEN LOWER(severity) IN ('critical', 'high') THEN 1 ELSE 0 END) > 0.8 ORDER BY urgent_pct DESC, svc_name ``` > **Common Pitfall** > > Filtering on the raw critical count, or dividing criticals by COUNT(*). Both answer a different question. A chatty service that pages only on 'high' has zero criticals and must drop out; a service whose pages are all critical must stay, even when criticals are only two thirds of its total traffic. Only the critical-over-paging ratio separates them, and only a bar above the critical-of-all ceiling forces the scoped denominator. > **Interviewers Watch For** > > Whether the denominator you divide by is the paging subset or the whole table, and whether LOWER() shows up before you are asked. Saying 'critical as a share of the high-or-critical alerts, not of everything' out loud is the tell that you read the question rather than pattern-matched the keywords. > **Cost Analysis** > > One sequential scan, hash-aggregated by svc_name over a few dozen services, so memory stays flat and there is no join to fan out. The three CASE expressions are per-row branches, cheap at any scale. At billions of alert rows this is still a single pass; if the table were partitioned by fired_at you would add a date predicate to prune, but nothing here asks for a window. --- ## Common follow-up questions - Only count alerts from the last 30 days. Where does that predicate go, and why not in HAVING? _(Tests that a row-level time filter belongs in WHERE before grouping, and awareness of partition pruning on fired_at.)_ - Add a minimum-volume guard so a service with two alerts, both critical, does not top the board at 100 percent. _(Pushes a second HAVING condition (a minimum paging count) and a conversation about statistical noise in small samples.)_ - Report the high-only share alongside the critical share for each qualifying service in the same pass. _(Probes conditional aggregation across multiple buckets while keeping one scan.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_noise_floor) - [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.