Median Failure Rate by Table

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain: SQL
Difficulty: hard
Seniority: L5
Asked in: Walmart

Problem

The data-quality team's triage sheet keeps flagging the same tables in dq_checks, yet a few catastrophic runs drag each table's average failure percentage far from what a typical check on it sees. Report every table with the median fail_pct across its checks, rounded to the nearest whole number.

Half the tables fail more than this.

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

dq_checks: 10 of 200 rows
check_id	tbl_name	col_name	rule	passed	fail_pct	run_at	severity
131	users	email	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	email	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

First 5 of 7 rows
tbl_name	median_fail_pct
ad_impressions	65
events	80
orders	0
products	90
sessions	20

Worked solution and explanation

A median wearing a triage costume

This is an exact per-group median in an engine that has no median function. Everyone can partition dq_checks by tbl_name. What separates candidates is what happens next. The weak answer writes ROUND(AVG(fail_pct)) and calls it a day. That is the mean, and fail_pct is heavily skewed, so a handful of blown-up runs drags every table's number toward the tail. The half-fix picks one middle row with rn = cnt / 2. That works when a table has an odd number of checks and is off by one value when it has an even number. Get either wrong and the medians for exactly those tables come out wrong.

Break down the requirements

Step 1: Number each table's values in order

Inside a subquery, give every check its position with ROW_NUMBER() OVER (PARTITION BY tbl_name ORDER BY fail_pct) and carry the table's size alongside it with COUNT(*) OVER (PARTITION BY tbl_name). Both windows share the same partition, so the engine sorts each table once.

Step 2: Keep only the middle row or rows

Filter to rn IN ((cnt + 1) / 2, (cnt + 2) / 2). For a table with 7 checks both expressions give 4. For a table with 8 they give 4 and 5. One predicate, no branching on parity.

Step 3: Average the survivors and round

GROUP BY tbl_name and take AVG(fail_pct) over the one or two rows left. Here AVG is safe because it only ever sees the center of the distribution. ROUND then gives the whole number the statement asks for.

The solution

Exact median per table, no median function

SELECT tbl_name,
       ROUND(AVG(fail_pct)) AS median_fail_pct
FROM (
  SELECT tbl_name,
         fail_pct,
         ROW_NUMBER() OVER (PARTITION BY tbl_name ORDER BY fail_pct) AS rn,
         COUNT(*)    OVER (PARTITION BY tbl_name)                    AS cnt
  FROM dq_checks
  WHERE fail_pct IS NOT NULL
)
WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2)
GROUP BY tbl_name

Common follow-up questions

How would you return the 90th percentile of `fail_pct` per table with the same building blocks? (Tests whether the candidate can generalize the middle-index formula to any percentile.)
What does your query return for a table with a single check? (Tests that the two-index selection degrades cleanly to one row.)
If `fail_pct` could be `NULL`, where should those rows be excluded, and why does it matter for `cnt`? (Tests that NULLs must be dropped before counting, or the middle index shifts.)