Noisiest Tables by 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

Google

Problem

Every row of dq_checks is one run of a quality rule against a table's column, recording whether the check passed and, for a failure, the percentage of rows that broke the rule. Show which tables the failures pile up on, with how many of each table's checks failed and the worst failure percentage among those failed checks, most failed checks first.

The tables that fail the most checks.

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 filtered rollup dressed up as data quality monitoring. Everyone writes the GROUP BY tbl_name; what separates candidates is where the failure scope goes and which column defines a failure. dq_checks holds every run, passing and failing, and each table here ran 28 or 30 checks while only 4 or 6 of them failed. Group before you scope and failure_count becomes a count of checks run, so users and products top the list just for running the most checks.

Building it

Step 1: Scope to failed checks first

WHERE passed = 0 runs before grouping, so every aggregate after it sees failures only. That one predicate turns COUNT(*) into a failure count and keeps a table with no failures from forming a group at all.

Step 2: Group on tbl_name alone

One row per table. col_name, rule and severity are not part of the grain; adding any of them splits a table's failures across several rows.

Step 3: Count and take the worst in one pass

COUNT(*) counts the failed checks and MAX(fail_pct) takes the worst percentage among them. Both read the same filtered rows, so they cannot disagree about which checks they describe.

Step 4: Sort by failure volume

ORDER BY failure_count DESC puts the noisiest tables first. Six tables tie at 6 failures; the question only asks for volume order, so their order among themselves is free.

SELECT
    tbl_name,
    COUNT(*) AS failure_count,
    MAX(fail_pct) AS max_fail_pct
FROM dq_checks
WHERE passed = 0
GROUP BY tbl_name
ORDER BY failure_count DESC

Count everything, branch inside

SUM(CASE WHEN passed = 0 THEN 1 ELSE 0 END) gets the count right, but every table still forms a group, so a table with no failures would come back with a zero. MAX(fail_pct) also ranges over passing rows: correct here only because they store NULL or 0.0.

Filter, then aggregate

WHERE passed = 0 scopes both aggregates at once, drops tables without failures, and sends only failing rows into the grouping.

Common follow-up questions

• How would you add the number of failed checks per severity level for each table? (Tests normalizing `severity` casing with `LOWER` before turning the counts into columns.)
• Tables with no failures should now appear with a count of zero. What changes? (Tests moving the scope from `WHERE` into a conditional aggregate, and what that does to `MAX(fail_pct)`.)
• Only the latest run of each rule on each column should count. How do you restrict to it? (Tests keeping one row per `tbl_name`, `col_name` and `rule` by `run_at` before aggregating.)