The Noise Floor

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

CrowdStrike

Problem

An on-call lead is combing alert_events for the services that page on genuine emergencies rather than crying wolf, where a real emergency is a critical-severity alert and severity is logged with inconsistent capitalization. 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.

Not every alert deserves a 3 a.m. page. Find the services where most of them claim they do.

Tables

alert_events

Columns: alert_id INTEGER, svc_name TEXT, severity TEXT, status TEXT, fired_at TEXT, ack_by TEXT, resolved TEXT

Expected output

Worked solution and explanation

What this is really asking

This is a conditional ratio whose denominator is itself a conditional count, dressed up as alert hygiene. Anyone can count criticals per service. What separates candidates is what they divide by: the keep-rule is criticals over the high-or-critical alerts, not over everything. Every service on this data also fires medium and low alerts, so criticals over all alerts never clear 0.692. Divide by COUNT(*) and the 80 percent bar keeps nothing, so the four services whose pages are all critical vanish. Compare severity case-sensitively and half the criticals and highs disappear, so every share you report comes out roughly halved.

The trap lives in the denominator

Criticals over all alerts

SUM(critical) / COUNT(*) peaks at 0.692 on this data ('gateway') because every service also fires medium and low alerts. An 80 percent bar against it returns an empty result, and lowering the bar only measures overall critical volume, not how a service 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 pages on 'high'. That ratio separates the two families, and the 80 percent bar 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. A case-sensitive match drops half the criticals and highs, which skews both the reported share and the keep-rule.

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(*). Multiplying by 100.0 keeps the division floating point; round to one decimal, as the preview shows.

Step 3: Filter on critical dominance, scoped to the pages

The keep-rule compares two conditional counts in HAVING: criticals over the high-or-critical count must exceed 0.8. The * 1.0 forces decimal division. Without it, engines that divide integers truncate a 79 percent service to 0, and the filter only works by accident.

Step 4: Sort for the on-call reader

ORDER BY urgent_pct DESC puts the loudest services on top. 'auth-svc' and 'db-primary' tie at 66.7, and either order is correct: nothing in the question puts one above the other, so no tiebreaker is owed.

The solution

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

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 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 several buckets while keeping one scan.)