Alert Hotspots by Service and Severity

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

City and County of San Francisco

Problem

Find how many alerts each service raised at each severity level in our alert_events log, noisiest pairings first. Severities were typed in by hand, so 'Critical' and 'critical' are the same level.

Some services and severities light up more than others.

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

This is a two-key count in an on-call costume, and the real test is whether you read the data before you trust the labels. Anyone can count per svc_name and severity. What separates candidates is noticing that severity was typed by hand: 'Critical' sits next to 'critical', 'HIGH' next to 'high'. Group the raw label and you get 24 rows where every critical and high service shows up twice at 8, while the real hotspots, 'payment-api' high and 'gateway' critical at 18 alerts each, never make the list.

Getting to the answer

Step 1: Read the labels before you group

A quick look at the values in severity returns six spellings for four levels. That makes the grouping key LOWER(severity), not the column. This one function is the difference between 16 honest rows and 24 split ones.

Step 2: Group by service and the folded level

The grain is one row per service per level, so both svc_name and LOWER(severity) go in the GROUP BY. Group on the expression rather than the alias: some engines resolve a bare severity in GROUP BY to the raw column and quietly bring the split back.

Step 3: Count every alert

COUNT(*) with no WHERE. A silenced or resolved alert still paged someone, so a status filter undercounts exactly the noise the team is trying to measure.

Step 4: Sort by the count alone

ORDER BY alert_count DESC is the whole order the question asks for. Six pairings tie at 16 and any order among them is correct, so a tiebreaker adds nothing.

SELECT
  svc_name,
  LOWER(severity) AS severity,
  COUNT(*) AS alert_count
FROM alert_events
GROUP BY svc_name, LOWER(severity)
ORDER BY alert_count DESC

Raw label

GROUP BY svc_name, severity returns 24 rows. 'gateway' shows 'Critical' at 10 and 'critical' at 8, and the top of the list is a four-way tie at 10.

Folded label

GROUP BY svc_name, LOWER(severity) returns 16 rows. 'gateway' critical is 18, tied with 'payment-api' high as the noisiest pairing.

Common follow-up questions

• Next week a value ' critical' with a leading space shows up. What breaks, and what do you add? (Tests whether the candidate sees `TRIM` as the same class of fix as case folding.)
• On-call only wants pairings with at least 15 alerts. Where does that condition go? (Tests filtering groups on an aggregate rather than filtering rows.)
• How would you show each pairing's share of its service's total alerts? (Tests carrying a per-service total next to the per-pairing count.)
• The team now wants this per month. What changes in the grain? (Tests adding a truncated `fired_at` as a third grouping key.)