Service Alert Frequency

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

Spotify

Problem

Build a leaderboard of the services in our alert_events log by alert volume, showing each service's alert count and its place in the standings. We read it noisiest first, with services on the same count listed alphabetically.

How often does each service trigger alerts?

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 tie-aware standings problem dressed up as alert triage. Anyone can count alerts per service with GROUP BY svc_name. What separates candidates is the place column. On this seed more than one service shares the top count and more than one shares the next, and the preview shows the rule: tied services share a place and the next count takes the very next number. Number the rows one after another and tied services get different places. Let a tie skip numbers and the second tier lands well below 2. Either way every place after the first tie is wrong, while the counts and the order still look perfect.

Reading the rule off the preview

Step 1: Count every alert per service

COUNT(*) over alert_events, grouped by svc_name. Nothing asks you to filter by status or severity, and severity arriving as both 'Critical' and 'critical' is a distraction here: every row is one alert, so the count never reads those columns.

Step 2: Number the totals, not the rows

The place belongs to a service, so the window orders by COUNT(*) DESC. Windows are evaluated after GROUP BY, which is why the aggregate is legal inside OVER (...) and one statement is enough.

Step 3: Pick the numbering the preview shows

The tied top services all show 1 and the next count shows 2. That is DENSE_RANK(). RANK() would jump past the size of the tied block, and ROW_NUMBER() would split it into 1, 2, 3.

Step 4: Order the output by what you return

Noisiest first, ties alphabetically: ORDER BY occurrence_count DESC, svc_name. Both keys are returned columns, so the order is checkable straight from the result.

SELECT svc_name, COUNT(*) AS occurrence_count,
       DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
FROM alert_events
GROUP BY svc_name
ORDER BY occurrence_count DESC, svc_name

Skipping after a tie

RANK() gives every tied top service 1, then hands the second tier one more than the size of that tie. The numbers look plausible and are wrong for every service below the top.

Continuing after a tie

DENSE_RANK() gives every tied top service 1 and the second tier 2, which is exactly what the preview shows.

Common follow-up questions

• How would you return only the services in the top two places, ties included? (Filtering on a window result needs a wrapping query or `QUALIFY`, and ties must survive the cut.)
• How do the standings change if only alerts nobody acknowledged (`ack_by` is `NULL`) count? (Conditional counting before ranking, on a column that really holds `NULL`s.)
• What if the team wanted standings within each severity? (Partitioning the window, and normalising 'Critical' vs 'critical' with `LOWER(severity)` first.)