Top Alert Resolvers

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

Netflix

Problem

An alert in alert_events counts as closed once it carries a resolution timestamp, and the incident response team credits each closure to whoever acknowledged it, leaving unacknowledged alerts off the board. Surface the three engineers with the most closures, from most to least, with equal tallies going to the alphabetically earlier name.

The engineers who resolve the most.

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 really tests

This is a filtered count-per-person hiding behind an ops leaderboard, with a tie sitting right on the cutoff. Anyone can group by ack_by and count rows. The separation happens in three places. First, resolved is a timestamp, not a flag, and status disagrees with it on plenty of rows: an alert can read 'resolved' with no stamp, or 'firing' with one. Second, 34 stamped alerts have no acknowledger. Third, alice, bob and eve all close 20 behind charlie's 22, so the tie rule decides who makes the board. Trust the label and you credit closures that never happened. Forget the NULL acknowledger and a nameless bucket tops the standings. Skip the tie rule and the third name is a coin flip.

Break down the requirements

Step 1: Define closed by the timestamp

An alert is closed when it carries a resolution timestamp, so the closure test is resolved IS NOT NULL in the WHERE clause. Do not lean on status = 'resolved': the two signals contradict each other in the data, and the timestamp is authoritative.

Step 2: Exclude the unattributed alerts

Credit belongs to a person, so ack_by IS NOT NULL drops the unattributed rows. Without it, the aggregate folds 34 stamped, unacknowledged alerts into a single NULL group that sits at the top of the board.

Step 3: Count closures per acknowledger

After the WHERE clause, every surviving row is exactly one closure credited to one engineer. Group by ack_by and tally those credits with SUM(1) to get each engineer's resolved_count.

Step 4: Sort, settle the tie, then cut

ORDER BY resolved_count DESC, ack_by puts the busiest closers first. The second key is not cosmetic: three engineers sit at 20 and only two seats remain, so the alphabetical rule is what keeps alice and bob and drops eve before LIMIT 3 cuts.

The solution

SELECT
    ack_by,
    SUM(1) AS resolved_count
FROM alert_events
WHERE ack_by IS NOT NULL
  AND resolved IS NOT NULL
GROUP BY ack_by
ORDER BY resolved_count DESC, ack_by
LIMIT 3

Common follow-up questions

• How would you also show each engineer's average time to resolution, from `fired_at` to the resolution timestamp? (Tests adding a computed aggregate over timestamp differences to the same grouped query.)
• If an `alert_id` shows up more than once with different timestamps, how do you make sure each alert is counted at most once? (Tests deduplication awareness: a repeated `alert_id` may need its latest row picked before counting.)
• Leadership wants everyone tied at the cutoff on the board instead of exactly three names; what changes? (Tests whether the candidate can swap a hard cut for a tie-inclusive one.)