Where the Data Breaks

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

mid

Asked in

Zoox

Problem

Our automated data-quality suite writes one row per check it runs, and we need to see where records are breaking. For each table that has failing checks, report how many checks failed and how many of those were critical or high severity, most failures first.

Every dataset has a weak point. Surface the ones the checks already caught.

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 problem is really testing

Strip off the data-quality costume and this is a conditional count nested inside a group: one total per table, plus a filtered sub-total on the same rows. Anyone can write COUNT(*) grouped by table. The two things that separate candidates are (1) noticing that severity is entered inconsistently ('high', 'HIGH', 'Critical', 'critical') so a raw equality filter silently undercounts, and (2) keeping the high-severity tally on the SAME failing rows without a second scan or a join. Miss the casing and your 'critical or high' column reports a number that is quietly too low, which is the worst kind of wrong on a triage dashboard: it looks plausible.

Break down the requirements

Step 1: Keep only the failures

The prompt says 'where records are breaking', so scope to checks that did not pass with WHERE passed = 0. Everything downstream, both counts, is measured on this filtered set.

Step 2: One row per table

GROUP BY tbl_name. Because the suite writes one row per check, COUNT(*) over the group is literally 'how many checks failed' for that table. No DISTINCT gymnastics needed.

Step 3: Normalize, then count the severe ones

SUM(CASE WHEN LOWER(severity) IN ('high', 'critical') THEN 1 ELSE 0 END) folds the casing variants together and counts the severe failures inside the same GROUP BY pass. The CASE returns 1/0, so the SUM is a count of matches.

Step 4: Order for triage

ORDER BY failing_checks DESC puts the noisiest tables on top. Add tbl_name as a tiebreaker so tables with equal failure counts come out in a stable, reproducible order.

The solution

SELECT tbl_name,
       COUNT(*) AS failing_checks,
       SUM(CASE WHEN LOWER(severity) IN ('high', 'critical') THEN 1 ELSE 0 END) AS high_severity_failures
FROM dq_checks
WHERE passed = 0
GROUP BY tbl_name
ORDER BY failing_checks DESC, tbl_name

Common follow-up questions

• Now weight each table by the average `fail_pct` of its failing checks, not just the raw count. How does the query change? (Tests moving from COUNT to AVG(`fail_pct`) and reasoning about NULL `fail_pct` on passing rows already excluded by the filter.)
• Only surface tables where more than half of the failing checks are high or critical. Where does that condition go? (Pushes toward HAVING with a ratio of two conditional sums, and the realization that it cannot live in WHERE.)
• The suite runs hourly. How would you report only the most recent run per table so stale failures do not inflate the counts? (Tests windowing or a correlated max(`run_at`) subquery to pick the latest batch before aggregating.)