Where Quality Breaks

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

Deloitte

Problem

Each row of dq_checks is one quality check run against a source table, with passed recording whether it flagged a problem and fail_pct the share of values that failed. Looking only at the flagged checks, show the source tables whose average fail percentage is above 30, worst first, with how many flagged checks each has and how many different rules they involved.

Which tables the checks keep flagging.

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 the data quality framing away and this is a filtered grouped aggregation whose last cut is a condition on each group, not on any row. The real skill is knowing where each cut belongs. passed = 0 is a fact about one row, so it goes in WHERE. The above-30 cut is a fact about a table's average, and that average does not exist yet when WHERE runs. Write WHERE avg_fail_pct > 30 and the query errors. Push the cut onto fail_pct row by row and you quietly keep and drop the wrong tables. The other quiet bug is reaching for COUNT(*) when the report asks how many different rules flagged the table. On this data every surviving table has 6 flagged checks but only 3 rules.

Build it in four moves

Step 1: Scope to the flagged checks

WHERE passed = 0 keeps only the checks that flagged a problem. Do not lean on fail_pct being blank instead: passing checks are not reliably empty, and some log 0.0. The flag column is the honest filter, and everything after this sees flagged rows only.

Step 2: Bucket by table

GROUP BY tbl_name collapses the flagged rows into one row per source table. Lock this grain in before writing the aggregates, because the average and both counts are computed within it.

Step 3: Average, and count the right thing

AVG(fail_pct) gives each table its average fail rate, and COUNT(DISTINCT rule) counts how many different rule labels flagged it. COUNT(*) answers a different question (how many flagged rows), and the report wants both numbers, so keep both.

Step 4: Filter on the average, worst first

HAVING AVG(fail_pct) > 30 keeps only the tables whose own average clears the bar. Here that drops sessions at 20 and orders at 7.5. A trailing ORDER BY avg_fail_pct DESC puts the worst table on top.

The query

SELECT tbl_name,
       COUNT(*) AS failed_checks,
       COUNT(DISTINCT rule) AS distinct_rules,
       AVG(fail_pct) AS avg_fail_pct
FROM dq_checks
WHERE passed = 0
GROUP BY tbl_name
HAVING AVG(fail_pct) > 30
ORDER BY avg_fail_pct DESC

Count the rows (wrong)

COUNT(*) returns how many flagged checks a table has (6 here), not how many different rules were involved. If the ask is rule variety, this silently over-reports.

Count different rules (right)

COUNT(DISTINCT rule) returns 3 for these tables: the number of different rule labels that flagged them. That is the variety the report wants.

Common follow-up questions

• How would you also report the total number of checks each table ran, passing and flagged, without changing which tables clear the cutoff? (Tests keeping passing checks out of the average while still counting them.)
• If the business wanted the worst 10 percent of tables by fail rate instead of a fixed 30 percent, how would you write it? (Tests moving from a fixed literal threshold to a relative one.)
• When would folding the rule casing with `LOWER` change the different-rule count, and how would you decide whether to do it? (Tests awareness of when casing normalization changes an answer.)
• How would your query change if `fail_pct` were stored as a fraction between 0 and 1 rather than a percentage? (Tests unit awareness and where a scaling factor belongs.)