# 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 review and wants one table showing how each service's alerts break down by severity. For every service, show the service name with a count of its alerts at each severity level (critical, warning, info, and unlabeled), plus a total alert count. Sort by total alerts from highest to lowest, breaking ties alphabetically by service name. ## 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 need to spread across columns, one count per level, with the service as the row key. Everyone can see they need a count per service. What separates candidates is HOW they bucket each severity into its own column in a single pass, and how they treat the unlabeled (NULL) alerts. The trap is reaching for COUNT(CASE WHEN ...) with an ELSE branch. Do that and every bucket inflates to the service's full alert count, because COUNT counts any non-NULL value, including the 0 you wrote in the ELSE. The numbers look plausible and they are all wrong. > **Trick to solving** > > Use SUM(CASE WHEN THEN 1 ELSE 0 END), not COUNT. SUM adds the 1s and ignores the 0s, giving you exactly the count of rows that matched the condition. Pair it with GROUP BY svc_name and you get one row per service with each severity isolated into its own column, all in one scan of the table. **Conditional aggregation pivot** ```sql SELECT svc_name, SUM(CASE WHEN severity = 'critical' THEN 1 ELSE 0 END) AS critical_count, SUM(CASE WHEN severity = 'warning' THEN 1 ELSE 0 END) AS warning_count, SUM(CASE WHEN severity = 'info' THEN 1 ELSE 0 END) AS info_count, SUM(CASE WHEN severity IS NULL THEN 1 ELSE 0 END) AS null_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 severity carved out by its own CASE expression.* #### Step 1: Group by the row key first GROUP BY svc_name collapses every alert for a service into a single output row. Everything in the SELECT list is now an aggregate computed over that service's slice of rows. This is the spine of the pivot: the dimension you group on becomes the rows, the conditions you write become the columns. #### Step 2: Carve each severity into its own column Each CASE expression returns 1 when a row matches that severity and 0 otherwise. SUM rolls those up per service, so critical_count is literally the number of critical rows for that service. Repeat the pattern for warning and info. The columns are independent because each CASE only fires on its own value. #### Step 3: Count the unlabeled bucket with IS NULL Alerts with no severity are NULL, and severity = 'critical' will never match a NULL because NULL compared to anything is unknown, not true. To bucket the unlabeled ones you must say severity IS NULL. Forget this and those rows vanish from every severity column yet still inflate total_count, which is the asymmetry an interviewer probes for. #### Step 4: Total and order COUNT(*) gives the grand total per service independent of severity, which doubles as a sanity check: the four buckets should sum to it. Then sort by total_count descending, and break ties on svc_name ascending so the output is deterministic when several services have the same count. > **Common pitfall** > > COUNT(CASE WHEN severity = 'critical' THEN 1 ELSE 0 END) returns the service's TOTAL alert count, not its critical count, because COUNT tallies every non-NULL value and 0 is non-NULL. If you keep COUNT you must drop the ELSE: COUNT(CASE WHEN severity = 'critical' THEN 1 END) returns NULL otherwise and is correct. SUM with ELSE 0 is the safer, clearer idiom and the one to reach for by default. **Wrong: COUNT with ELSE 0** COUNT(CASE WHEN severity = 'critical' THEN 1 ELSE 0 END). Every row yields either 1 or 0, both non-NULL, so COUNT counts ALL of them. Every severity column equals total_count. Looks symmetric, completely wrong. **Right: SUM with ELSE 0** SUM(CASE WHEN severity = 'critical' THEN 1 ELSE 0 END). The 0s contribute nothing to the sum, so only matching rows add up. Each column holds its true per-severity count. > **Interviewers watch for** > > Two tells signal seniority here: reaching for SUM(CASE ...) over COUNT(CASE ...) without being prompted, and handling NULL severity with IS NULL rather than an equality that silently drops those rows. A candidate who explains why = NULL never matches has shown they understand three-valued logic, which is the real concept under the alert dressing. > **Performance insight** > > This is a single sequential scan plus a hash aggregate, O(n) over the table with no self-joins and no subqueries. Adding more severity columns is free: each is just another CASE in the same pass. The alternative shape, one filtered query per severity UNIONed or joined together, scans the table once per bucket and is strictly slower for the identical result. ## Common follow-up questions - The severity values in production arrive as a mix of 'critical', 'Critical', and 'CRITICAL'. How do you make the buckets case-insensitive? _(Tests whether they normalize with LOWER(severity) inside the CASE rather than enumerating every spelling.)_ - Leadership now wants the percentage 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/alert_severity_pivot_by_service) - [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.