Low Severity DQ Checks
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
- City and County of San Francisco
Problem
Return every field of each data quality check in dq_checks that is classified as 'low' severity.
Low severity checks. All of them.
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
| check_id | tbl_name | col_name | rule | passed | fail_pct | run_at | severity |
|---|---|---|---|---|---|---|---|
| 193 | transactions | status | format | 1 | NULL | 2026-04-04 03:00:00 | low |
| 379 | products | amount | unique | 1 | NULL | 2026-01-10 09:00:00 | low |
| 565 | users | UNIQUE | 0 | 45 | 2026-07-16 15:00:00 | low | |
| 751 | orders | user_id | freshness | 1 | 0 | 2026-04-22 21:00:00 | low |
| 937 | ad_impressions | region | format | 1 | NULL | 2026-01-28 03:00:00 | low |
Worked solution and explanation
What this really is
This is a label lookup dressed up as an audit. The skill being probed is seeing that 'low severity' is a value stored in its own column, severity, not something you infer from how badly a check failed. Anyone can write a WHERE. What separates candidates is not reaching for a proxy. Filter on a small fail_pct or on passed = 1 and you get a plausible set of quiet checks that overlaps the 34 'low' checks only by accident, so the audit reviews checks nobody ever classified as low.
Step 1: Find where the tier lives
Scan the sample rows: severity holds the tier as text, next to passed and fail_pct. Those two describe how a run went, not how the check is classified. Check 193 passed with no failure percentage and is 'low'; check 131 did exactly the same and is 'high'.
Step 2: Decide whether the label needs cleaning
severity mixes case on the higher tiers ('HIGH' beside 'high', 'Critical' beside 'critical'), so it is fair to ask whether 'low' does too. It does not: every low check is stored as lowercase 'low', so the exact match is complete. LOWER(severity) = 'low' returns the same 34 rows here. It is harmless, but it is not the fix for anything.
Step 3: Return the row as stored
The ask is every field, so all eight columns come back unchanged, including rule values like 'UNIQUE' beside 'unique' and the empty fail_pct on 24 of the 34 rows. The audit asks for neither to be cleaned, and touching them changes the answer.
The solution
SELECT check_id, tbl_name, col_name, rule, passed, fail_pct, run_at, severity
FROM dq_checks
WHERE severity = 'low'Common follow-up questions
- The team now wants, per `tbl_name`, how many low-severity checks failed. What changes? (Moves from a row filter to grouping with a conditional count on `passed`.)
- If 'Low' and ' low ' start appearing tomorrow, how do you keep the filter correct without merging tiers that should stay apart? (Tests normalizing with `LOWER(TRIM(severity))` and knowing which spellings it should merge.)
- How would you limit the audit to checks run in the last 30 days? (Tests a date bound on `run_at`, which is stored as text.)