Open Wounds

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Google

Problem

During an incident review, the on-call team needs the oldest unresolved alert for each service. Walk the services in alphabetical order, showing each one's name, the alert ID, the severity, the status, and the time the alert was fired.

Every service has one alarm that was never silenced. Find the one that has been screaming the longest.

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 a per-group earliest-row pick wearing an incident-management costume, and the row has to come back whole: id, severity and status of the winning alert, not just its time. Everyone lands the window function. What separates candidates is the single word 'unresolved'. Two columns describe closure and they disagree: an operator can flip status to 'resolved' while the resolved timestamp was never written, and leave status at 'firing' on an alert closed hours ago. Trust the label and you drop alerts that are still bleeding and crown closed ones as the oldest open. The timestamp is the authority: open means resolved IS NULL.

Reading the requirements

Step 1: Filter to genuinely open alerts

WHERE resolved IS NULL keeps only alerts nobody ever closed. It never touches status, because the label and the timestamp contradict each other in this data.

Step 2: Number oldest-first within each service

ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY fired_at ASC) numbers each service's open alerts from oldest to newest, so the earliest fired alert per service gets 1. ROW_NUMBER (not RANK) hands out exactly one 1 per service even if two alerts fired at the same instant.

Step 3: Keep the winner per service

Wrap it in ranked_open_alerts and keep WHERE rn = 1. The whole row (id, severity, status) rides along with the winning fired_at, which a per-service MIN(fired_at) cannot give you without a second trip back to the table.

Step 4: Order the output by service

ORDER BY svc_name gives the reviewer the alphabetical read of the board the statement asks for.

The solution

SELECT svc_name, alert_id, severity, status, fired_at
FROM (
    SELECT
        svc_name, alert_id, severity, status, fired_at,
        ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY fired_at ASC) AS rn
    FROM alert_events
    WHERE resolved IS NULL
) AS ranked_open_alerts
WHERE rn = 1
ORDER BY svc_name

status <> 'resolved'

Reads the label. db-primary's alert 274 says 'resolved' with a null resolved timestamp, so it is still open, and this filter throws it away. On the seed db-primary's true oldest open alert, 10003006, is labelled 'resolved' too, so the service gets the wrong winner, while gateway's alert 216 reads 'firing' despite being closed and gets let in.

resolved IS NULL

Reads the authority. It keeps exactly the alerts no one ever closed, whatever the status text claims, and admits none that were closed. Status is still shown in the output, just never used to decide.

Common follow-up questions

• Suppose a later migration lets an alert be reopened, clearing `resolved` back to NULL after it was set. Does your query still return the right oldest-open alert, and what would break if you had filtered on `status` instead? (Tests whether the filter is held to closure semantics rather than the label under a schema change.)
• If two open alerts for one service share the exact same `fired_at`, how many rows does your query return for that service, and how would you make the winner deterministic? (Tests `ROW_NUMBER` vs `RANK` vs `DENSE_RANK` tie behaviour and adding a tiebreaker such as `alert_id`.)
• Which index would you build so this stays fast at 30M rows, and why does column order in that index matter for both the filter and the per-service ordering? (Tests indexing intuition for a filtered, partitioned, ordered scan at scale.)