The Ones That Woke Us

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

Problem

The reliability team is reviewing every serious incident from 2026. Pull the full record of each alert that fired that year at high or critical severity, counting those levels no matter how they were capitalized in the log.

The year's serious incidents, and nothing quieter.

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 looks like an incident report but it is really a filtered projection with two quiet traps. The skill being probed: can you match a categorical column that was logged in inconsistent case, and can you pull a calendar year out of a text timestamp? Anyone can write WHERE severity IN ('high', 'critical'). The catch is that the log holds 'HIGH', 'Critical' and 'high' side by side, and string equality is case sensitive, so a bare literal list silently drops every row whose casing does not match. Get that wrong and you hand the reliability team an under-count of the year's incidents while your query still looks correct.

Normalize before you compare

The fix is one function. Fold the column to a single case with LOWER(severity), then compare against a lowercase list. 'HIGH', 'High' and 'high' now collapse to the same token and match. Fold the COLUMN, not your literals: it is the stored data that varies, not your search terms.

SELECT *
FROM alert_events
WHERE LOWER(severity) IN ('high', 'critical')
  AND strftime('%Y', fired_at) = '2026';

Pulling the year from a timestamp

fired_at is a full timestamp stored as text, so comparing it to a bare year never matches. strftime('%Y', fired_at) reduces it to its four-digit year as a string, which you compare against '2026' (quoted, because the function returns text). EXTRACT(YEAR FROM fired_at) or a closed range from January 1 up to the next January 1 works just as well. What matters is that both ends of the year are bounded.

Naive filter

WHERE severity IN ('high', 'critical') AND fired_at >= '2026-01-01'. This misses every mixed-case severity, and the open-ended date bound would admit rows from any later year.

Robust filter

WHERE LOWER(severity) IN ('high', 'critical') AND strftime('%Y', fired_at) = '2026'. Severity matches regardless of case, and the year comes straight from the timestamp, so adjacent years cannot leak in.

Common follow-up questions

• How would you also include 'warning' severity but keep the results ordered with critical alerts first? (Tests extending the `IN` list plus a `CASE`-based custom sort order.)
• If `severity` casing keeps drifting, where would you fix it permanently instead of folding case in every query? (Probes moving normalization upstream: a `CHECK` constraint, an enum, or a cleaning step in the pipeline.)
• How would you count high and critical alerts per service for the year instead of listing every row? (Moves from a filtered projection to a grouped count over the same predicate, keyed on `svc_name`.)