Top Regions by Critical Alerts

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

L4

Problem

List the top 5 regions by number of critical alerts in alert_events, highest count first. An alert records only its service, so we place it in a region through that service's checks in svc_health.

Which regions have the highest volume of critical alerts

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

What this really is

This is a dirty-label filter plus a region lookup that lives in the wrong table, dressed up as a postmortem leaderboard. Anyone can write the join and the GROUP BY. What separates candidates is reading the severity sample rows before typing: 'critical' and 'Critical' are both there, and each is about half of the critical alerts. Filter on the lowercase spelling alone and every region's count halves, 'eu-west-1' lands level with 'eu-central-1' at 144, and the top of the list stops meaning anything.

The second decision is the grain. alert_events has no region, so the region has to come from svc_health, which holds many checks per service. 'auth-svc', 'user-svc' and 'gateway' each show up in two regions. The expected counts (308, 288, 160, 144) are per alert-to-check match, not per alert, so reconcile against the preview before you reach for a dedupe.

Step 1: Filter severity across both spellings

WHERE ae.severity IN ('critical', 'Critical') keeps both casings the data holds. LOWER(ae.severity) = 'critical' would also survive a third spelling arriving later, and on this data it matches the same rows. Either way, the filter sits on the alert side so the join only ever sees critical rows.

Step 2: Attach the region from svc_health

Inner-join alert_events to svc_health on svc_name. 'db-primary' fires critical alerts but has no health checks, so it has no region to report under and the inner join drops it. Every critical alert pairs with every check of its service, which is exactly the grain the preview counts.

Step 3: Count the matches per region

GROUP BY sh.region with COUNT(*) AS critical_count counts the joined pairs. 'ap-south-1' never appears: none of the services that both fire critical alerts and have checks is checked there.

Step 4: Cut at five, keeping ties

RANK() OVER (ORDER BY COUNT(*) DESC) numbers the regions by volume, and rnk <= 5 keeps every region tied at the fifth spot instead of letting LIMIT 5 drop one arbitrarily. On this data only four regions have critical alerts, so all four come back, highest count first.

SELECT region, critical_count
FROM (
  SELECT
    sh.region,
    COUNT(*) AS critical_count,
    RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
  FROM alert_events ae
  INNER JOIN svc_health sh
    ON ae.svc_name = sh.svc_name
  WHERE ae.severity IN ('critical', 'Critical')
  GROUP BY sh.region
) ranked
WHERE rnk <= 5
ORDER BY critical_count DESC;

One spelling

WHERE ae.severity = 'critical' keeps half the critical rows: 'eu-central-1' 144, 'eu-west-1' 144, 'us-east-1' 80, 'us-west-2' 64. The top two tie and every count is off by half.

Both spellings

ae.severity IN ('critical', 'Critical') keeps them all: 'eu-central-1' 308, 'eu-west-1' 288, 'us-east-1' 160, 'us-west-2' 144. That is the result that grades.

Follow-up questions

Common follow-up questions

• How would the query change if each alert should count once per region instead of once per check? (Tests whether you can move the grain deliberately, by deduplicating `svc_health` to (`svc_name`, `region`) or counting distinct `alert_id`.)
• Rewrite this so ties at the fifth spot still return exactly five rows, broken by region name. (Probes `ROW_NUMBER` versus `RANK` and a deterministic tiebreaker in the window's `ORDER BY`.)
• If `region` were NULL for some checks, how should that appear in the output? (Checks whether you treat NULL as its own bucket, a filter or an error, and whether you say which.)
• Same question, but only for alerts fired in the last 24 hours: where does the predicate on `fired_at` go? (Probes filter pushdown on the alert side before the fan-out join.)