Broken Promises Between Tables
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
- City and County of San Francisco
Problem
dq_checks holds one row per validation run, and its rule names were logged without any casing standard, so the not-null rule shows up spelled more than one way. Count how many not-null checks failed (passed is 0), returned as a single fail_count.
Every foreign key is a pinky-swear. Count the ones that got broken.
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
| fail_count |
|---|
| 8 |
Worked solution and explanation
What this one is really asking
This is a data-cleaning problem wearing a one-line COUNT(*). Anyone can filter passed = 0. The thing that separates candidates is whether they believe the warning about casing: on this table the not-null rule is logged both as 'not_null' and as 'Not_Null'. Write rule = 'not_null' and the query runs, returns a believable number, and is wrong by every row someone logged with capitals.
Step 1: Fold the label before you match it
LOWER(rule) turns 'Not_Null' and 'not_null' into the same string. Do it on the column side, then match against an all-lowercase pattern, and the two spellings stop being two buckets.
Step 2: Match the family, not one spelling
LIKE '%null%' on the folded value catches the not-null rule however it was punctuated, and no other rule on the table contains that substring. Say that check out loud: a pattern is only safe when you have confirmed what else it could catch.
Step 3: Let the flag define failure
passed = 0 is the outcome. fail_pct is NULL on most rows, so a fail_pct > 0 filter silently drops failures that never recorded a percentage.
SELECT COUNT(*) AS fail_count
FROM dq_checks
WHERE LOWER(rule) LIKE '%null%'
AND passed = 0- Trusts the label
WHERE rule = 'not_null' AND passed = 0binds to one spelling. Every 'Not_Null' failure vanishes, and nothing warns you.- Normalizes the label
WHERE LOWER(rule) LIKE '%null%' AND passed = 0folds both spellings first, so the count covers the whole rule.
Common follow-up questions
- How would you break this out by `tbl_name` to show which tables have the most empty keys? (Probes moving from a scalar count to a grouped one without losing the normalization.)
- How would you turn the raw count into a failure rate for the not-null rule? (Probes conditional counting over all not-null runs, failed and passed.)
- `severity` is also logged in mixed casing. How would you count only the critical failures? (Probes whether the candidate applies the same fold to a second dirty column.)