Sirens and Smoke

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

ESPN

Problem

Which alerts in alert_events fired during 2026 at high or critical severity, however the letters of that severity happen to be cased? Return each matching alert's full row exactly as it was stored.

Stale alerts. Still ringing.

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

This is a dirty-enum filter dressed up as an incident audit. Everyone writes a year filter and a two-value severity list; what separates candidates is noticing that severity was never normalized on write, so the same level arrives as 'high', 'HIGH', 'critical' and 'Critical'. Write severity IN ('high', 'critical') and the query runs, returns believable rows, and silently drops every 'HIGH' and 'Critical' alert: on this table that is half of the serious incidents the audit exists to find.

Read the values before you write the filter

Step 1: Look at what severity actually holds

The sample rows already give it away: alert 216 is 'Critical', alert 245 is 'HIGH', and alert 129 is plain 'high'. A column called severity with four levels in the schema can still hold six spellings in the data. Treat any free-text enum as untrusted until you have seen its values.

Step 2: Fold the spellings, then compare

LOWER(severity) IN ('high', 'critical') collapses four spellings into the two levels the audit cares about. The literals must be lowercase too, because you are comparing against the folded value, not the stored one.

Step 3: Take the year out of the timestamp

fired_at holds text like '2026-02-02 01:07:00', so comparing it to a bare year matches nothing. strftime('%Y', fired_at) = '2026' reads the year off each timestamp. The table spans 2022 through 2026, so forgetting this conjunct quietly pulls four years of older alerts into the audit.

Step 4: Return the rows as they were stored

The cleaning lives in the filter, not the output: severity comes back exactly as the pager sent it, which is what the preview shows ('Critical' next to 'critical'). An audit should show the raw event, not a rewritten one.

SELECT alert_id, svc_name, severity, status, fired_at, ack_by, resolved
FROM alert_events
WHERE LOWER(severity) IN ('high', 'critical') AND strftime('%Y', fired_at) = '2026'

Exact match

severity IN ('high', 'critical') keeps only the lowercase rows. 'HIGH' and 'Critical' fail the comparison, so the result is half the size it should be and nothing errors.

Case-folded match

LOWER(severity) IN ('high', 'critical') matches all four spellings, and every serious 2026 alert comes back, whatever case its producer used.

Common follow-up questions

• How would you fix this at the source so no consumer needs `LOWER` again? (Tests whether the candidate thinks about normalizing on write or with a constraint rather than patching every reader.)
• Rewrite the year filter so the engine can prune partitions on `fired_at`. (Tests turning an extracted-year comparison into a half-open range on the raw timestamp.)
• What changes if `severity` can also be `NULL` or carry trailing spaces? (Tests whether the candidate knows `NULL` never matches an `IN` list and that case folding alone misses padded values.)