DQ Fail Rate by Table
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
- L4
- Asked in
- Airbnb
Problem
Some of our data quality checks record a measured fail percentage; the rest were never scored and leave it blank. For each table, find the share of checks that passed among only the ones carrying a recorded fail percentage.
Pass rates, table by table.
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
| tbl_name | pass_rate |
|---|---|
| ad_impressions | 0 |
| events | 0 |
| orders | 0.8571428571428571 |
| products | 0 |
| sessions | 0 |
Worked solution and explanation
What this really tests
This is a filtered average wearing a data-quality costume. The skill being probed: compute each table's pass share, but only across the checks that actually carry a recorded fail_pct. Anyone can write AVG(passed) grouped by tbl_name. The catch is that AVG skips nulls only in its OWN argument, passed, which is fully populated here, so it will happily fold in every unscored check whose fail_pct is null. Leave off WHERE fail_pct IS NOT NULL and you average passed over all the checks instead of the roughly one-third that were scored, and every table's rate drifts toward its whole-table figure (ad_impressions reads 0.79 instead of the correct 0.0) while the query runs without a single error.
Walk the requirements
Step 1: Bound the population to scored checks
WHERE fail_pct IS NOT NULL keeps only the checks that recorded a fail percentage. This is the load-bearing line: about two-thirds of dq_checks were never scored (fail_pct is null) and all of them passed, so leaving them in inflates every table's pass share.
Step 2: Take the pass share per table
GROUP BY tbl_name with AVG(passed). Since passed is 1 for a pass and 0 for a fail, its average over the scored checks is exactly the fraction that passed.
The solution
SELECT tbl_name, AVG(passed) AS pass_rate
FROM dq_checks
WHERE fail_pct IS NOT NULL
GROUP BY tbl_nameCommon follow-up questions
- How would you instead report the pass share only for high-severity checks? (Tests swapping the IS NOT NULL predicate for a severity filter such as severity = 'high' while keeping the same aggregate shape.)
- What changes if you wanted the average `fail_pct` of the scored checks rather than the pass share? (Tests understanding that AVG(`fail_pct`) auto-skips its own nulls, so the explicit filter is only needed here because the aggregated column differs from the filter column.)
- How would you surface only the tables where fewer than half the scored checks passed? (Tests HAVING on the aggregate, e.g. HAVING AVG(passed) < 0.5.)