Below the Line
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
Severity in dq_checks is a free-text label rather than a fixed list, and every row is one check run stamped with its full run_at timestamp. Count the 2026 runs that came in at the 'low' severity tier.
Low severity. High volume.
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
What they are really testing
This is a year-scoped count dressed up as a data-quality report. Everyone writes the severity filter; the probe is run_at. It is a full text timestamp, and dq_checks reaches from 2022 through 2026, so the year has to be pulled out of it before you compare. Compare the whole timestamp to '2026' and you get 0, which reads like a quiet year instead of a bug. Forget the year entirely and you get 34, because 14 low runs from earlier years ride along with the 20 that belong to 2026.
Step 1: Pull the year out of run_at
strftime('%Y', run_at) turns each timestamp into its four-digit year, and that is what you compare to '2026'. A timestamp is not a year: equality against the full string can never match.
Step 2: Pin the low tier
severity is free text: other tiers arrive in two casings ('Critical' and 'critical', 'HIGH' and 'high'). On this data the low tier only ever arrives as 'low', so LIKE 'low%' and = 'low' agree. The prefix pattern keeps the filter from depending on that staying true.
Step 3: Count every surviving run
With both conditions in the WHERE, COUNT(*) over what remains is the answer. Each row is one execution, so repeated runs of the same rule each count, and passed and failed runs count alike.
SELECT COUNT(*) AS low_severity_count
FROM dq_checks
WHERE severity LIKE 'low%'
AND strftime('%Y', run_at) = '2026';The prefix pattern pins the low tier; strftime pulls the year out of run_at before comparing.
- Compares the timestamp to the year
WHERE severity = 'low' AND run_at = '2026'asks whether '2026-04-04 03:00:00' equals '2026'. It never does, so the count is 0.- Extracts the year first
WHERE severity LIKE 'low%' AND strftime('%Y', run_at) = '2026'compares year to year, so the 20 low runs of 2026 survive and the earlier years drop out.
Common follow-up questions
- Break the low-tier count down by year instead of filtering to one. (Moves the extracted year from a filter into the grouping key.)
- Return the count per severity tier, with 'HIGH' and 'high' landing in one tier. (Tests normalizing a dirty label before grouping on it.)
- How would the answer change if `run_at` were UTC but the report used local time? (Probes awareness that year extraction is timezone-sensitive.)