The Quiet Alarms
A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- medium
- Seniority
- L3
- Asked in
- Vesta Innovations
Problem
The analyst retuning severity thresholds thinks the dashboard's figure for the 'low' tier in 2026 looks too quiet to be real. Count the checks in dq_checks that actually ran at that severity during the year.
Low severity. High volume. Worth a look.
Tables
dq_checks
Columns: check_id INTEGER, tbl_name TEXT, col_name TEXT, rule TEXT, passed INTEGER, fail_pct REAL, run_at TEXT, severity TEXT
| check_id | tbl_name | col_name | rule | passed | fail_pct | run_at | severity |
|---|---|---|---|---|---|---|---|
| 131 | users | unique | 1 | NULL | 2026-02-02 01:00:00 | high | |
| 162 | products | amount | range | 1 | NULL | 2026-03-03 02:00:00 | medium |
| 193 | transactions | status | format | 1 | NULL | 2026-04-04 03:00:00 | low |
| 224 | events | created_at | referential | 1 | NULL | 2026-05-05 04:00:00 | Critical |
| 255 | sessions | price | freshness | 0 | 15 | 2026-06-06 05:00:00 | HIGH |
| 286 | ad_impressions | region | Not_Null | 1 | NULL | 2026-07-07 06:00:00 | critical |
| 317 | orders | user_id | UNIQUE | 1 | 0 | 2026-08-08 07:00:00 | high |
| 348 | users | not_null | 1 | NULL | 2026-09-09 08:00:00 | medium | |
| 379 | products | amount | unique | 1 | NULL | 2026-01-10 09:00:00 | low |
| 410 | transactions | status | range | 0 | 30 | 2026-02-11 10:00:00 | Critical |
Expected output
| low_severity_count |
|---|
| 20 |
Worked solution and explanation
This is a one-number count, and the whole question hides in one column: run_at. It reads like a date filter, but run_at holds full timestamps ('2026-04-04 03:00:00' style), so the year has to be pulled out before you compare it. Write run_at = '2026' and you get 0. Drop the year and you count every low check since 2022: 34 instead of 20. Either way the team retunes its severity thresholds on a number that is not this year's.
Getting to the count
Step 1: Read the shape of run_at before filtering on it
run_at is TEXT laid out as 'YYYY-MM-DD HH:MM:SS'. The year is in there, but not as a value you can test for equality. strftime('%Y', run_at) hands back just those four characters as text, which you can compare to '2026'.
Step 2: Match the tier with plain equality
severity = 'low' is the whole tier. The casing noise in severity lives in other tiers ('Critical', 'HIGH'), not in 'low', so wrapping it in LOWER(severity) returns the same 20 and only adds work. A LIKE '%low%' is worse: it would pick up any future value that merely contains the letters.
Step 3: Count what survives both filters
COUNT(*) over the filtered rows is the volume. Alias it low_severity_count so the dashboard column explains itself.
SELECT COUNT(*) AS low_severity_count FROM dq_checks WHERE severity = 'low' AND strftime('%Y', run_at) = '2026'- Raw timestamp compared
WHERE severity = 'low' AND run_at = '2026'compares '2026' to strings like '2026-04-04 03:00:00'. Nothing is equal, so the count is 0, which looks like a quiet tier rather than a broken query.- Year pulled out first
strftime('%Y', run_at) = '2026'keeps every low check that ran any time that year: 20 rows, out of 34 low checks across all five years.
Common follow-up questions
- How would you rewrite the year filter so an index on `run_at` could be used, instead of the per-row `strftime` call? (Tests the sargable rewrite: `run_at >= '2026-01-01' AND run_at < '2027-01-01'` keeps the raw column on one side of the comparison.)
- How would you return a count for every severity tier in 2026, not just 'low'? (Tests grouping on a dirty key: grouping on `LOWER(severity)` keeps 'Critical'/'critical' and 'HIGH'/'high' from splitting into separate tiers.)
- How would you also report how many of those low checks failed (`passed = 0`)? (Tests conditional counting: `SUM(CASE WHEN passed = 0 THEN 1 ELSE 0 END)` alongside the total, in one pass.)