The Severity Matrix

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

Problem

Each row of alert_events is one alert a service fired, and its severity was typed by hand, so the same level turns up in different capitalizations that all mean one level. Produce one row per service with its alert count at each severity level plus its total, busiest services first.

When services cry wolf, the numbers reveal who's serious.

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 really is

Strip off the on-call costume and this is a pivot with dirty keys: svc_name is the row key, the four severity levels become columns, and every cell is a count. Anyone can write the pivot. What separates candidates is noticing that severity was typed by hand, so 'critical' and 'Critical' are one level, and so are 'high' and 'HIGH'. Compare against the raw value with severity = 'critical' and every capitalized alert falls out of its bucket. The totals still look right, because COUNT(*) never looks at severity. That is exactly why the bug survives review: the level columns quietly stop adding up to the total, and nobody checks.

Step 1: Group by the row key first

GROUP BY svc_name collapses every alert for a service into one output row, so each expression in the SELECT becomes an aggregate over that service's slice. The dimension you group on becomes the rows; the conditions you write become the columns.

Step 2: Fold the casing, then carve each level

Wrap severity in LOWER before comparing, so 'Critical' and 'HIGH' land with their lowercase twins. Each CASE returns 1 when the folded value matches its level and 0 otherwise. Every CASE fires only on its own value, so the columns stay independent.

Step 3: Sum the flags, do not count them

SUM rolls the 1s and 0s into each service's count for that level. COUNT would be wrong here: it tallies every non-NULL value, and the 0 from the ELSE branch is non-NULL, so COUNT(CASE WHEN ... THEN 1 ELSE 0 END) returns the full alert total in every column. Use SUM with ELSE 0, or COUNT with no ELSE.

Step 4: Total, then busiest first

COUNT(*) gives each service's total regardless of severity and doubles as a sanity check: the four buckets must add up to it, and on this data they do only once the casing is folded. Sort by total_count descending. Several services tie on the same total, but the review only needs the busiest on top, so no further sort key is needed.

SELECT svc_name,
       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 LOWER(severity) = 'medium' THEN 1 ELSE 0 END) AS medium_count,
       SUM(CASE WHEN LOWER(severity) = 'low' THEN 1 ELSE 0 END) AS low_count,
       COUNT(*) AS total_count
FROM alert_events
GROUP BY svc_name
ORDER BY total_count DESC

Case-sensitive: `severity = 'critical'`

Matches only the lowercase spelling. 'Critical' rows fall through every bucket and surface only in total_count, so critical_count reads far lower than reality.

Case-folded: `LOWER(severity) = 'critical'`

Every capitalization of the level lands in one bucket, so critical_count is the true number of critical alerts and the four buckets sum to total_count.

Common follow-up questions

• Some alerts arrive with no severity at all. How do you add a column that counts those, and why can't `severity = ''` catch them? (Tests `IS NULL` handling and three-valued logic: a missing severity is NULL, and only `severity IS NULL` matches it.)
• Leadership now wants the share of each service's alerts that were critical, not just the raw count. How does the query change? (Tests dividing a conditional `SUM` by `COUNT(*)` while guarding against integer division.)
• If a new severity level appears next quarter, what breaks, and how would you design so it does not? (Tests awareness that hardcoded `CASE` columns are brittle, and that a dynamic pivot or a long-format contract may be the better choice.)