# The Severity Matrix > When services cry wolf, the numbers reveal who's serious. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem The on-call lead is prepping the weekly reliability review and needs one table showing, for each service, how its alerts split across the severity levels plus a total count. Severity is typed in by hand and the same level turns up in different capitalizations, so treat those as one; put the busiest services at the top. ## Worked solution and explanation ### What this really is Strip off the on-call costume and this is a pivot: the severity column holds the values you spread across columns, one count per level, with the service as the row key. The wrinkle that separates candidates is the data itself. Severity is typed by hand, so the same level arrives as 'critical', 'Critical', and 'CRITICAL'. Reach for severity = 'critical' and you match only the exact lowercase spelling, silently dropping every capitalized variant, so each bucket undercounts. The totals still look plausible, which is exactly why the bug survives review: the noisiest service can read as one of the quietest. > **Trick to solving** > > Normalize the case inside each bucket with LOWER(severity), then count with SUM(CASE WHEN ... THEN 1 ELSE 0 END). LOWER folds every spelling of a level together; SUM adds the 1s and ignores the 0s, so each column is the true count for that severity. One GROUP BY svc_name and the whole matrix falls out in a single scan. **Case-folded conditional aggregation pivot** ```sql 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, svc_name ASC ``` *One pass, one GROUP BY, each level carved out by a case-folded CASE expression.* #### Step 1: Group by the row key first GROUP BY svc_name collapses every alert for a service into one output row. Each expression in the SELECT is then 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 you compare, so 'Critical', 'HIGH', and their lowercase twins land in the same bucket. Each CASE returns 1 when the folded value matches its level and 0 otherwise. Because every CASE only fires on its own value, the columns stay independent. #### Step 3: Sum the flags, do not count them SUM rolls the 1s and 0s into the matching-row count for each service. Do not use COUNT here: COUNT tallies every non-NULL value, and the 0 in the ELSE branch is non-NULL, so COUNT(CASE WHEN ... THEN 1 ELSE 0 END) returns the full alert total in every column. SUM with ELSE 0, or COUNT with no ELSE, is what you want. #### Step 4: Total and order COUNT(*) gives the per-service grand total independent of severity and doubles as a sanity check, since the buckets should sum to it. Sort by total_count descending and break ties on svc_name ascending so the output is deterministic when services share a count. > **Common pitfall** > > The quiet killer is case-sensitive equality: severity = 'critical' skips 'Critical' and 'CRITICAL', so every bucket undercounts while the totals stay believable. Its cousin is COUNT(CASE WHEN ... THEN 1 ELSE 0 END), which inflates every column to the service total because COUNT counts the 0s too. Fold case with LOWER and count with SUM and both traps disappear. **Case-sensitive: severity = 'critical'** Matches only the exact lowercase spelling. 'Critical' and 'CRITICAL' 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 collapses into one bucket, so critical_count is the true number of critical alerts for that service. > **Interviewers watch for** > > Two tells signal seniority: normalizing case with LOWER (or UPPER) without being reminded that hand-entered labels are messy, and choosing SUM(CASE ...) over COUNT(CASE ...) so the ELSE branch cannot corrupt the counts. A candidate who eyeballs the sample, spots the mixed casing, and adjusts before writing the pivot is thinking like an operator, not a syntax machine. > **Performance insight** > > This is one sequential scan plus a hash aggregate, O(n) over the table with no self-joins and no subqueries. Extra severity columns are free: each is just another CASE in the same pass. The alternative shape, one filtered query per level UNIONed together, scans the table once per bucket for the identical answer and is strictly slower. ## 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 you catch them with severity = '' or severity = 'none'? _(Tests IS NULL handling and three-valued logic: unlabeled alerts are NULL and only severity IS NULL captures them.)_ - 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(*) and guarding against integer division and zero rows.)_ - 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 grouped long format may be the better contract.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_severity_matrix) - [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.