Non-Bot Acknowledged Alerts
An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- easy
- Seniority
- L3
- Asked in
- TransUnion
Problem
During on-call incident review, pull all alerts that were not acknowledged by 'alice', including alerts that were never acknowledged at all. Show all fields, and walk the incident timeline from the earliest alert that fired through to the most recent.
Human-acknowledged alerts only.
Tables
alert_events
Columns: alert_id INTEGER, svc_name TEXT, severity TEXT, status TEXT, fired_at TEXT, ack_by TEXT, resolved TEXT
| 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
| alert_id | svc_name | severity | status | fired_at | ack_by | resolved |
|---|---|---|---|---|---|---|
| 10003001 | payment-api | high | acknowledged | 2022-01-02 01:07:00 | bob | 2022-01-02 03:11:00 |
| 10003061 | notif-svc | high | acknowledged | 2022-01-06 13:07:00 | bob | 2022-01-06 15:11:00 |
| 10003026 | user-svc | medium | resolved | 2022-02-03 02:02:00 | bob | 2022-02-03 04:46:00 |
| 10003051 | search-api | low | silenced | 2022-03-24 03:57:00 | bob | NULL |
| 10003016 | auth-svc | Critical | firing | 2022-04-17 16:52:00 | NULL | 2022-04-17 18:56:00 |
Worked solution and explanation
Why this problem exists in real interviews
This focuses on query construction within alert_events, specifically around the svc_name column. Interviewers present it as a fundamentals check because the edge cases around NULL values and boundary conditions reveal depth of understanding.
Break down the requirements
Step 1: Apply the NOT-alice filter with NULL awareness
WHERE ack_by != 'alice' OR ack_by IS NULL ensures both non-alice and never-acknowledged alerts are included.
Step 2: Order by fired_at
ORDER BY fired_at sorts results chronologically as requested.
Step 3: Return all fields
SELECT * returns the complete alert record.
The solution
SELECT *
FROM alert_events
WHERE ack_by != 'alice' OR ack_by IS NULL
ORDER BY fired_atCommon follow-up questions
- What happens to your result if `alert_events`.severity contains NULLs for some rows? (Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on severity.)
- How would you verify that your aggregation on `alert_events`.alert_id is not double-counting due to duplicate rows? (Tests data quality awareness and deduplication strategies.)
- With millions of distinct values in `alert_events`.alert_id, what index strategy would you use to keep this query performant? (Tests indexing knowledge specific to high-cardinality columns like `alert_id`.)