# Open Wounds > Every service has one alarm that was never silenced. Find the one that has been screaming the longest. Canonical URL: 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. ## Tables ### alert_events - alert_id INTEGER - svc_name TEXT - severity TEXT - status TEXT - fired_at TEXT - ack_by TEXT - resolved TEXT Sample rows (10 of 200): | alert_id | svc_name | severity | status | fired_at | ack_by | resolved | | --- | --- | --- | --- | --- | --- | --- | | 129 | payment-api | high | acknowledged | 2026-02-02 01:07:00 | bob | 2026-02-02 03:11:00 | | 158 | user-svc | medium | resolved | 2026-03-03 02:14:00 | charlie | 2026-03-03 04:22:00 | | 187 | search-api | low | silenced | 2026-04-04 03:21:00 | dana | NULL | | 216 | gateway | Critical | firing | 2026-05-05 04:28:00 | NULL | 2026-05-05 06:44:00 | | 245 | notif-svc | HIGH | acknowledged | 2026-06-06 05:35:00 | alice | 2026-06-06 07:55:00 | | 274 | db-primary | critical | resolved | 2026-07-07 06:42:00 | bob | NULL | | 303 | cache-01 | high | silenced | 2026-08-08 07:49:00 | charlie | 2026-08-08 09:17:00 | | 332 | auth-svc | medium | firing | 2026-09-09 08:56:00 | NULL | 2026-09-09 10:28:00 | | 361 | payment-api | low | acknowledged | 2026-01-10 09:03:00 | eve | NULL | | 390 | user-svc | Critical | resolved | 2026-02-11 10:10:00 | alice | 2026-02-11 12:50:00 | ## Expected output First 5 of 8 rows: | svc_name | alert_id | severity | status | fired_at | | --- | --- | --- | --- | --- | | auth-svc | 10003096 | critical | firing | 2022-12-13 00:12:00 | | cache-01 | 10003087 | low | silenced | 2023-03-04 15:09:00 | | db-primary | 10003006 | critical | resolved | 2022-06-07 06:42:00 | | gateway | 10003036 | critical | firing | 2022-12-09 12:12:00 | | notif-svc | 10003021 | low | acknowledged | 2022-09-22 21:27:00 | ## 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 **Single-pass per-service oldest open alert** ```sql 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 ``` > **Closure lives in the timestamp** > > The entire problem lives in the WHERE clause. `status` is a mutable label an operator can flip; `resolved` is written only when the alert is actually closed. The prompt asks about closure, so `resolved IS NULL` is the whole question and the numbering is plumbing around it. **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. > **Cost at 30M rows** > > With `alert_events` at 30,000,000 rows the window pass still scans everything under `resolved IS NULL`. A composite index on `(resolved, svc_name, fired_at)` lets the engine skip closed rows and feed each partition already ordered, so the sort collapses. Finding each service's `MIN(fired_at)` and joining back reads the table twice. > **The label lies, and the display is not yours to clean** > > The killer is `status <> 'resolved'` or its mirror. Label and timestamp disagree by design: 'resolved' with a null timestamp, 'firing' with one set. Second trap: severity casing ('critical', 'Critical', 'HIGH') is display-only and never grouped on, so an `UPPER(severity)` rewrites values the grader compares exactly as stored. > **Ask which column decides closure** > > The strong tell is a candidate who asks 'does `status` decide closure, or the `resolved` timestamp?' before writing anything, then filters on the timestamp. Reaching for `status` without questioning it is the exact miss this problem is built to surface. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/open_wounds) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.