# Where Quality Breaks > Which tables the checks keep flagging. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem Data quality checks only record a fail percentage when they flag a problem, so a check that flagged one always has a recorded rate. For each source table, take just those flagged checks and report how many there were, how many different rules they involved, and their average fail rate, then keep only the tables averaging above 30 percent. ## Worked solution and explanation ### What this problem is really testing Strip the data quality framing away and this is a filtered grouped aggregation with a condition applied to the groups, not the rows. The real skill is knowing where each cut belongs. A check only records a fail percentage when it flags a problem, so `WHERE passed = 0` scopes you to exactly the rows that carry a rate. Then each table gets an average, and the above-30 cut is a statement about that average, which does not exist yet when a `WHERE` clause runs. Try to write `WHERE avg_fail_pct > 30` and the query errors or, worse, you push it onto individual rows and quietly keep the wrong tables. The other quiet bug: reaching for `COUNT(*)` when the report asks how many different rules flagged the table. Rows and distinct rules are not the same number. > **The cutoff is a fact about the group** > > The 30 percent test looks at a table's average, so it can only run after the rows are grouped and averaged. That is what `HAVING` is for. `WHERE` filters rows before any grouping happens and never sees a group's average. --- ### Build it in four moves #### Step 1: Scope to the flagged checks `WHERE passed = 0` keeps only the checks that flagged a problem. Those are the rows with a recorded `fail_pct`, so passing checks and their blank percentages drop out before anything is averaged or counted. #### 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 both the average and the 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), so keep both if the report wants both numbers. #### Step 4: Filter on the average `HAVING AVG(fail_pct) > 30` keeps only the tables whose own average clears the bar, then a trailing `ORDER BY avg_fail_pct DESC, tbl_name` presents the ugliest table on top with a deterministic tie-break on name. --- ### The query **Average fail rate per table, flagged checks only, above the 30 percent bar** ```sql 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, tbl_name ``` **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 distinct 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. > **Casing is real data here** > > The rule labels arrive in mixed casing, so a table logs both `unique` and `UNIQUE`. Take the label as recorded and they count as two rules; fold the casing with `LOWER(rule)` and your distinct count drops below what the data shows. Match the counts in the expected preview before assuming casing should be normalized. > **What the interviewer is watching** > > Whether the 30 percent cut goes in `HAVING` rather than `WHERE`, and whether you reach for `COUNT(DISTINCT rule)` instead of `COUNT(*)` when asked about rule variety. Both are the difference between a query that runs and a query that is right. > **Cheap at scale** > > The table is 500K rows across a couple hundred tables. The `WHERE` trims to the flagged rows, the `GROUP BY` collapses them to a handful of group rows, and only then does `HAVING` run, so the plan stays a single scan plus a small sort. ## Common follow-up questions - How would you also report the total number of checks each table ran, passing and failing, without changing which tables clear the cutoff? _(Tests whether the candidate keeps passing checks out of the average while still reporting a total.)_ - 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 with a window or subquery.)_ - When would folding the rule casing with LOWER actually change the distinct-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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where_quality_breaks) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.