Regions by Alert Volume

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

We're building an incident heatmap by region, and on-call wants the noisiest regions surfaced first. Count the alerts per region (based on the service's region), from the most incidents down to the fewest, and when two regions carry the same count list them alphabetically.

Some regions are quiet. Others never stop screaming.

Tables

alert_events

Columns: alert_id INTEGER, svc_name TEXT, severity TEXT, status TEXT, fired_at TEXT, ack_by TEXT, resolved TEXT

svc_health

Columns: check_id INTEGER, svc_name TEXT, status TEXT, latency REAL, uptime REAL, checked TEXT, region TEXT

Expected output

Worked solution and explanation

This is a dimension lookup dressed up as a heatmap. alert_events knows which service fired, never where it runs; region exists only on svc_health, so the real work is carrying that attribute over before anything gets tallied. Anyone writes the GROUP BY. What separates candidates is checking the grain of the table they borrowed from: svc_health is a log of checks, not a service directory, so each alert meets every check its service logged, and some services log from two regions. Skip that and the 688 at the top of the preview looks like a bug when the log holds only 200 alerts; 'fix' it with a dedup and you ship a different heatmap from the one on-call reads.

Getting there

Step 1: Find where region lives

alert_events carries svc_name and nothing geographic. The only region column is on svc_health, keyed by the same svc_name, so the service name is the bridge. A per-service count of alert_events is a dead end: there is nothing to roll it up by.

Step 2: Join on svc_name and accept what drops

An inner join keeps only services that have health checks. 'db-primary' and 'cache-01' fire 48 alerts between them but never appear in svc_health, so they have no region and leave the map. That is the intended behavior: a heatmap cannot place what it cannot locate.

Step 3: Name what one joined row is

After the join, a row is an alert paired with one health check of its service, not an alert. Every matched service has 18 checks: 'auth-svc' logs 10 from 'us-east-1' and 8 from 'eu-west-1', so its 24 alerts become 240 rows in one region and 192 in the other. The 152 matched alerts turn into 2,736 rows, exactly the sum of the preview.

Step 4: Tally per region, noisiest first

COUNT(*) grouped by sh.region counts those pairings. Sort by alert_count DESC, then by region: three regions tie at 468, and the alphabetical key puts 'ap-south-1' ahead of 'eu-central-1' and 'us-west-2'.

SELECT sh.region, COUNT(*) AS alert_count
FROM alert_events ae
INNER
JOIN svc_health sh ON ae.svc_name = sh.svc_name
GROUP BY sh.region
ORDER BY alert_count DESC, region

One row per health check

Joining svc_health as it is weights each alert by how many checks its service logged in a region: 'us-east-1' reads 688.

One row per service and region

Collapsing to unique (svc_name, region) pairs first counts an alert once per region: 'us-east-1' reads 74. Same GROUP BY, different question.

Common follow-up questions

• How would you count each alert exactly once per region its service runs in? (Tests collapsing `svc_health` to unique service-region pairs before the join.)
• What if alerts whose service has no health check had to appear under an 'unknown' region? (Tests switching to an outer join and defaulting the missing `region`.)
• How would you produce the same totals without materializing the full alert-by-check join? (Tests pre-aggregating both sides by `svc_name` and multiplying the counts.)