# The Ones That Woke Us

> The year's serious incidents, and nothing quieter.

Canonical URL: <https://datadriven.io/problems/the_ones_that_woke_us>

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.

## 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 timestamp column? Anyone can write WHERE severity IN ('high', 'critical'). The catch is that the log holds 'HIGH', 'Critical', and 'high' side by side, and SQLite compares text case sensitively, so a bare literal list silently drops every row whose casing does not match. Get that wrong and you hand the reliability team a serious 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 your lowercase list. Now 'HIGH', 'High', and 'high' all collapse to the same token and match. Do the folding on the COLUMN, not on your literals, because it is the stored data that varies, not your search terms.

**Case-folded filter plus year extraction**

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

*LOWER() collapses the mixed-case severities; strftime pulls the year straight from the timestamp.*

### Pulling the year from a timestamp

fired_at is a full timestamp, so a plain equality against a year will never match. strftime('%Y', fired_at) formats the timestamp down to its four-digit year as a string, which you compare against '2026' (quoted, because strftime returns text). This avoids hardcoding a BETWEEN range and reads exactly like the business ask: alerts that fired in that year.

> **The silent case-sensitivity drop**
>
> WHERE severity IN ('high', 'critical') runs without error and returns rows, so it feels correct. But it skips 'HIGH', 'Critical', and any other casing, quietly halving the result. A query that errors gets fixed; a query that under-counts ships. Always fold the case of free-text status or severity columns before matching them.

> **What signals seniority here**
>
> A junior filters on the literal case they saw in one sample row. A senior asks 'how is severity stored, is it a controlled vocabulary or free text?' and normalizes defensively. Naming the case-sensitivity risk out loud, before being shown a failing row, is the tell that you have been burned by dirty categorical data before.

**Naive filter**

WHERE severity IN ('high', 'critical') AND fired_at >= '2026-01-01'. Misses every mixed-case severity, and the open-ended date bound can leak rows from the next year.

**Robust filter**

WHERE LOWER(severity) IN ('high', 'critical') AND strftime('%Y', fired_at) = '2026'. Case-insensitive on severity, and the year is derived directly from the timestamp so it cannot leak adjacent years.

> **In production**
>
> Alert and status enums drift over a service's lifetime: one exporter writes 'critical', another writes 'CRITICAL', a migration leaves 'Critical'. Analysts who assume a clean enum publish dashboards that undercount incidents. Case-folding at read time is the cheap insurance until the upstream data is cleaned.

## 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 aggregate over the same predicate.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_ones_that_woke_us)
- [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.