Tables With Most DQ Failures

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

Whether a data quality check failed is recorded by its passed flag in dq_checks, not by fail_pct. Count the failed checks per table and list the tables from the most failures down to the fewest.

The tables with the most failures.

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	fail_count
users	6
transactions	6
sessions	6
products	6
orders	4

Worked solution and explanation

This is a definition-of-failure question dressed up as a leaderboard. The grouping and the sort are the easy part. What separates candidates is deciding which column actually says a check failed, because dq_checks offers two that look like it: the passed flag and fail_pct. Only passed is the verdict. Key off fail_pct and the answer inverts: 24 passing 'orders' runs logged a 0.0 fail_pct, so counting rows where it is recorded puts 'orders' on top at 28, when it actually has the fewest failures of any table (4).

Pick the column that tells the truth

Step 1: Keep only runs where `passed = 0`

The sample rows already show it. Check 255 failed with fail_pct 15.0, check 317 passed with fail_pct 0.0, and most passing runs leave fail_pct empty. fail_pct is a measurement someone sometimes took; passed is the outcome. Filter on the outcome, before anything is counted.

Step 2: Count rows per `tbl_name`

Each row is one check run, so after the filter COUNT(*) is the number of failed runs per table. A rule that failed on two runs counts twice, which is what a compliance team means by failure volume.

Step 3: Sort worst first, and stop there

ORDER BY fail_count DESC is the whole ordering requirement. Six tables share a count of 6 here, and the question says nothing about how equals line up, so any order among them is correct; a tiebreaker adds nothing that was asked for.

Failed checks per table, worst first

SELECT tbl_name, COUNT(*) AS fail_count
FROM dq_checks
WHERE passed = 0
GROUP BY tbl_name
ORDER BY fail_count DESC

Counting every check: Without the filter, COUNT(*) returns 'products' and 'users' at 30 and everyone else at 28. That ranks tables by how often they are monitored, and 'orders' looks like an average table.
Counting failed checks: With WHERE passed = 0 first, six tables sit at 6 and 'orders' stands alone at 4. The one table that differs is exactly the one the unfiltered version hid.

Common follow-up questions

How would you list every table, including one with zero failures? (Tests moving from a row filter to a conditional count, so tables with no failures survive with 0.)
How would you weight each failure by `severity`, given the mixed casing in that column? (Tests normalizing a dirty categorical before mapping it to weights.)
Compliance only cares about the latest run of each check. What changes? (Tests keeping one row per `tbl_name`, `col_name` and `rule` by `run_at` before counting.)