Not All Fires Are Equal

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

Affirm

Problem

Severity labels in alert_events were typed by hand, so 'HIGH' and 'high' are the same level, and an on-call lead only trusts alerts from services that also report into svc_health. For each severity, she wants the average alert count across the service-severity pairs that fired more than once in 2026, as a decimal rounded to one place.

The alert volume varies. So does what it means.

Tables

alert_events

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

svc_health

Columns: check_id INTEGER, svc_name TEXT, status TEXT, latency REAL, uptime REAL, checked TEXT, region TEXT

Expected output

Worked solution and explanation

What this really is

This is an average of counts dressed up as alert hygiene. Anyone can count alerts per severity. The candidates who pass first pin down the population being averaged: one row per service and cleaned severity that fired more than once, and only for services the health system knows about. Collapse those two levels into one pass, or let svc_health fan the rows out, and you ship numbers that look reasonable and answer a different question.

Two quieter traps sit in the inner query. The severity has to be cleaned before counting, not after. The year you pull out of fired_at comes back as text, and text never equals the number 2026. Miss that and the report is empty, with no error to tell you why.

Building it

Step 1: Scope to the year, as a number

strftime('%Y', fired_at) returns text such as '2026'. Compared to the number 2026 it matches no row. CAST(... AS INTEGER) puts both sides in the same type, so the year bites before counting and older alerts stay out of every pair.

Step 2: Count each cleaned pair and drop one-offs

GROUP BY svc_name, LOWER(severity) with COUNT(*) AS alert_count, then HAVING COUNT(*) > 1. Lowering inside the grouping lets 'HIGH' and 'high' add up before the gate judges the pair.

Step 3: Gate on a deduplicated membership list

svc_health has a row per health check, so reduce it to one row per svc_name with its own GROUP BY before the INNER JOIN. Now the join removes unmonitored services like 'db-primary' and copies nothing.

Step 4: Average the pairs per severity

Outer GROUP BY combo.severity, then ROUND(AVG(combo.alert_count), 1). Each surviving pair counts once in its severity's mean, however many alerts it holds.

SELECT
  combo.severity,
  ROUND(AVG(combo.alert_count), 1) AS avg_alert_count
FROM (
  SELECT
    svc_name,
    LOWER(severity) AS severity,
    COUNT(*) AS alert_count
  FROM alert_events
  WHERE CAST(strftime('%Y', fired_at) AS INTEGER) = 2026
  GROUP BY svc_name, LOWER(severity)
  HAVING COUNT(*) > 1
) combo
INNER JOIN (
  SELECT svc_name
  FROM svc_health
  GROUP BY svc_name
) monitored
  ON combo.svc_name = monitored.svc_name
GROUP BY combo.severity

One pass over alerts

SELECT severity, COUNT(*) * 1.0 / COUNT(DISTINCT svc_name) FROM alert_events GROUP BY severity has nowhere to apply the per-pair HAVING COUNT(*) > 1, so single-alert pairs drag every mean down.

Count pairs, then average

The inner GROUP BY svc_name, LOWER(severity) materializes exactly the pairs that qualify; the outer GROUP BY severity averages that population and nothing else.

Common follow-up questions

• What happens to the averages if you join `svc_health` without reducing it to one row per `svc_name`? (Tests whether the candidate sees that duplication reweights a mean rather than inflating it.)
• Rewrite the year filter so a date-partitioned `alert_events` can skip partitions. (A range on `fired_at` instead of a function wrapped around it, which also removes the text-versus-number question.)
• How would you report the median count per severity instead of the mean? (Reuses the inner pair counts as a building block.)
• A new label 'Sev1' starts appearing alongside 'critical'. Where does that mapping live? (Moving from case cleaning to a reference mapping applied before the inner grouping.)