Extremely Late Resolutions

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

Asked in

General Assembly

Problem

What were the first three alerts each service fired, according to alert_events, and where does each one fall in its service's history? The on-call review team wants those alerts exactly as they were logged, every column in its original spelling, with the lower alert_id counting as earlier when two fired at the same moment.

Twenty minutes past the SLA. Still unresolved.

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 is

This is top-N-per-group dressed up as an on-call history review. Everyone sees 'first three' and types ORDER BY fired_at LIMIT 3, and that is the trap: LIMIT caps the whole result, so you get three alerts from whichever service fired first and nothing for the other services. The candidates who dodge that often swing to MIN(fired_at), which only ever finds the first alert. The skill being probed is whether you can number rows inside each service, keep the whole row, and hand back its position.

Limit the whole table

ORDER BY fired_at LIMIT 3 sorts every alert together and stops after three rows total. A MIN(fired_at) join-back or a single-row dedup does better on coverage but still returns one alert per service and no position.

Number inside each service

ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY fired_at, alert_id) restarts at 1 for every service. Keep rnk <= 3 and each service contributes its own three full rows, labeled 1, 2, 3.

Building it

Step 1: Partition by svc_name

PARTITION BY svc_name makes the count restart for every service. That is the per-service boundary LIMIT has no way to express: it only knows the total result.

Step 2: Order by fired_at, then alert_id

Order each partition by fired_at, then alert_id. Without the second key, two alerts fired in the same minute trade places run to run, and which one is 'second' becomes a coin flip the grader will not forgive.

Step 3: Keep rnk <= 3 in the outer query

A window alias cannot be filtered in the SELECT that computes it, so the numbering lives in the derived table ranked_alerts and the outer query keeps rnk <= 3. There is no filter on resolved and no cleanup of severity: every alert comes back as it was logged and holds its place in line.

SELECT alert_id, svc_name, severity, status, fired_at, ack_by, resolved, rnk
FROM (
    SELECT alert_id,
           svc_name,
           severity,
           status,
           fired_at,
           ack_by,
           resolved,
           ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY fired_at, alert_id) AS rnk
    FROM alert_events
) AS ranked_alerts
WHERE rnk <= 3

Common follow-up questions

• Now return only each service's second alert. What changes? (Tests whether the candidate sees the filter, not the window, changes.)
• Only alerts with `severity` 'critical' should count, but casing varies ('Critical', 'critical'). Does that filter go inside or outside `ranked_alerts`, and why does it matter? (Tests where a row filter sits relative to the numbering.)
• `fired_at` is TEXT. When does sorting it as a string stop matching chronological order? (Tests reasoning about `fired_at` stored as TEXT.)
• New alerts land every minute. Do the first three alerts per service ever change once set, and how would you exploit that? (Tests incremental thinking at scale.)