Alert Response Breakdown

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

L4

Asked in

City and County of San Francisco

Problem

Every row of alert_events is one fired alert, with severity recorded in inconsistent letter case and ack_by left empty when nobody acknowledged it. Across the whole table, each service needs its total alerts, how many were critical, how many were high, and how many were never acknowledged. Alongside those comes the average number of alerts per status category the service saw, to two decimal places, with the busiest services first and alphabetical among equal totals.

An on-call postmortem asks which services are bleeding alerts nobody acknowledges.

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

Why this problem exists in real interviews

This tests whether you reach for conditional aggregation on instinct. The wide output (critical_count, high_count, unacked_count side by side) is the tell. A junior writes three subqueries or a self-join; you collapse it into one pass over alert_events with SUM(CASE WHEN ...) per bucket. The LOWER(severity) requirement and the per-status average are the two places people slip.

Break down the requirements

Step 1: One pass, group by service

GROUP BY svc_name. Everything else, totals, severity buckets, unacked, per-status average, comes from a single scan over alert_events. No subqueries needed.

Step 2: Normalize severity in the CASE

LOWER(severity) = 'critical' inside each SUM(CASE WHEN ...). Cheap because the comparison happens row by row during the aggregate, not in a WHERE that would prune data.

Step 3: Unacked count via NULL

SUM(CASE WHEN ack_by IS NULL THEN 1 ELSE 0 END). Do not use COUNT(ack_by) and subtract from total; that works but obscures intent in a wide pivot.

Step 4: Avg alerts per status

COUNT(*) * 1.0 / COUNT(DISTINCT status) inside ROUND(..., 2). The 1.0 * forces float division so MySQL / Postgres do not truncate. COUNT(DISTINCT) is the second pass the planner needs.

Step 5: Order by volume

ORDER BY total_alerts DESC. The aggregate alias is fine to reference here in every major engine.

The solution

SELECT
  svc_name,
  COUNT(*) AS total_alerts,
  SUM(CASE WHEN LOWER(severity) = 'critical' THEN 1 ELSE 0 END) AS critical_count,
  SUM(CASE WHEN LOWER(severity) = 'high'     THEN 1 ELSE 0 END) AS high_count,
  SUM(CASE WHEN ack_by IS NULL              THEN 1 ELSE 0 END) AS unacked_count,
  ROUND(1.0 * COUNT(*) / COUNT(DISTINCT status), 2) AS avg_per_status
FROM alert_events
GROUP BY svc_name
ORDER BY total_alerts DESC, svc_name;

Pivot via conditional agg (idiomatic)

One pass, output already wide. SUM(CASE WHEN LOWER(severity)='critical' ...) per bucket, grouped only by svc_name. Hands the BI tool a ready table.

GROUP BY (`svc_name`, severity) then reshape

Long format: one row per (service, severity). Correct, but you need a PIVOT (Snowflake/SQL Server) or a wrapping conditional agg anyway to widen it. Two-step for no win.

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you extend this to a per-week breakdown without rewriting the aggregate? (Probes whether you add `DATE_TRUNC('week', fired_at)` to the GROUP BY and understand that the conditional-agg shape scales by adding grouping keys, not by rewriting.)
• What changes if severity values were normalized at ingest? (Tests whether you push data hygiene upstream rather than paying `LOWER()` on every read of a 50M-row table.)
• How would you compute the median time-to-acknowledge per service in the same pass? (Forces you to introduce `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ack_at - fired_at)` and discuss why it cannot fold into a plain SUM/CASE shape.)
• Why might `COUNT(DISTINCT status)` mislead the avg metric? (Checks whether you spot that a service with one observed status gets `total / 1`, inflating the average versus a service that happens to see all 5 statuses.)
• Rewrite this for a columnar warehouse where `severity` is dictionary-encoded. (Surfaces awareness that `LOWER()` defeats dictionary pushdown; the fix is to compare against the encoded set or normalize at load.)