Tables With Many DQ Failures

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

BuzzFeed

Problem

Find every table that has failed three or more data quality checks since April 1, 2026, with its failure count, worst offenders first. The data platform team is pulling this from dq_checks, which logs one row per check run along with whether it passed.

Some tables have never once passed QA.

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

Expected output

Worked solution and explanation

What this really is

This is a two-stage filter dressed up as a data quality report. The data gets asked two different questions: which rows count (a failed run since April 1) and which tables survive (three or more of those rows). The first is a fact about each row of dq_checks; the second only exists after counting. Anyone can write COUNT(*) per tbl_name. What separates candidates is putting each condition at the stage that can see what it needs. Drop the window and you get all-time noise, where six of seven tables tie at 6. Drop the threshold and ad_impressions and products land on the offenders list with a single failure each.

Walking to the answer

Step 1: Keep only failed runs inside the window

passed = 0 and run_at >= '2026-04-01' each describe a single check run, so both go in WHERE, before anything is counted. Leave out passed = 0 and every passing run since April inflates the tally. Leave out the window and you are counting since 2022.

Step 2: Count failures per tbl_name

GROUP BY dc.tbl_name with COUNT(*) collapses the surviving runs into one row per table. Because the rows are already narrowed to failures, COUNT(*) is the failure count. There is nothing to add up in fail_pct: it is a severity measure and is NULL on most rows.

Step 3: Apply the three-failure bar after counting

HAVING COUNT(*) >= 3 judges a table, not a run, so it can only be evaluated after GROUP BY. On this data it is the clause that removes ad_impressions and products, each with one failed run since April.

Step 4: Sort worst offenders first

ORDER BY fail_count DESC puts users and events (6 each) on top, then sessions 5, orders 4, transactions 3. The two leaders tie and either may come first, because the report only asks for worst first.

SELECT dc.tbl_name, COUNT(*) AS fail_count
FROM dq_checks dc
WHERE dc.passed = 0
  AND dc.run_at >= '2026-04-01'
GROUP BY dc.tbl_name
HAVING COUNT(*) >= 3
ORDER BY fail_count DESC

Threshold left out

Without HAVING COUNT(*) >= 3, all seven tables come back, including ad_impressions and products with one failure each: tables with a single blip get paged as offenders.

Threshold after counting

With it, five tables remain: users 6, events 6, sessions 5, orders 4, transactions 3. That is the list the platform team can act on.

Common follow-up questions

• How would you make the window a rolling last 90 days instead of a fixed April start? (Tests date arithmetic against an anchor instead of a literal boundary.)
• `rule` arrives as both 'UNIQUE' and 'unique'. How would you count failures per table and rule? (Tests normalizing a dirty key before grouping.)
• How would you report each table's failure rate in the window instead of its failure count? (Tests a conditional aggregate over all runs rather than a pre-filtered count.)