Alert Count by Severity Tier

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

L3

Asked in

City and County of San Francisco

Problem

How many alerts did each responder pick up across all of alert_events, whatever state the alert ended in? For our on-call load review, every alert nobody picked up counts together under 'unassigned'.

Alerts by severity. The breakdown matters.

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

This is a group-and-count where the missing value is a real category, dressed up as an on-call review. Anyone can count rows per ack_by; what separates candidates is noticing that 50 of the 200 alerts have no acknowledger at all, and that those alerts are the point: they are the load nobody carried. Fold them into one named 'unassigned' bucket with COALESCE and the answer is six clean rows. Group on the raw column and they land under a blank NULL key. Drop them with a filter, or count only alerts whose status is 'acknowledged', and the largest slice of on-call load quietly disappears from the review.

Break down the requirements

Step 1: Fold unacknowledged alerts into 'unassigned'

COALESCE(ack_by, 'unassigned') AS responder. An alert with no recorded acknowledger was never picked up by anyone, and the review tracks all of that unclaimed load together. Grouping on the raw column instead splits those rows into their own blank-named NULL group, which is exactly the coverage gap the review is trying to name.

Step 2: Count every alert, whatever its status

COUNT(*) on alert_events, with no filter on status. ack_by is filled on resolved and silenced alerts too; on this data only the 'firing' alerts carry no acknowledger. So the acknowledger column, not the status label, says who picked an alert up. COUNT(alert_id) gives the same number since alert_id is never null, but COUNT(*) states the intent.

Step 3: Group by the labeled responder

GROUP BY responder, so the folded rows collapse into the single 'unassigned' group. Grouping on the labeled expression, not the raw ack_by, is what keeps the unacknowledged alerts in one named bucket rather than under a nameless key.

Step 4: Return the whole distribution

No LIMIT and no date filter: the review covers every alert in the table, so all five responders and the 'unassigned' bucket come back. Row order is not part of the answer; any order of the six rows is correct.

The solution

SELECT
  COALESCE(ack_by, 'unassigned') AS responder,
  COUNT(*) AS alert_count
FROM alert_events
GROUP BY responder

Common follow-up questions

Common follow-up questions

• Break the same counts down by `severity` within each responder. (Adds a second grouping key and forces reasoning about grain, plus the mixed-case `severity` values that need `LOWER` first.)
• For each responder, also show how many of their alerts were eventually resolved. (Pushes toward `COUNT(*) FILTER (WHERE resolved IS NOT NULL)` or a conditional `SUM` alongside the total.)
• Same breakdown, but only for the last 24 hours. (Checks whether the candidate adds a `fired_at` predicate and recognizes that partition pruning now kicks in.)
• Add each responder's percentage of total alert volume. (Needs `COUNT(*) OVER ()` or a scalar subquery as the denominator.)