# Where the Data Breaks > Every dataset has a weak point. Surface the ones the checks already caught. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: mid ## 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. ## 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 **Failing checks per table** ```sql 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 ``` > **The conditional-count pattern** > > SUM(CASE WHEN THEN 1 ELSE 0 END) is the workhorse for 'total, plus how many of them also satisfy X' in a single group. It reuses the rows you already scanned for COUNT(*), so you get two metrics for one pass instead of self-joining the table to itself or running a second grouped query and stitching results. > **Common Pitfall** > > Writing severity IN ('high', 'critical') without LOWER. The suite stores 'HIGH' and 'Critical' too, so the raw filter drops those rows and your high_severity_failures column reads low. On a dashboard nobody notices a number that is merely too small, which is exactly why interviewers plant messy casing in the fixture. > **Interviewers Watch For** > > Whether you ask 'is passed a clean 0/1 or does it have nulls?' before trusting WHERE passed = 0, and whether you normalize severity unprompted. Bonus signal: recognizing that COUNT(*) equals the check count only because the grain is one row per check, and saying so out loud. > **Cost Analysis** > > At 200 rows this is a single scan and a hash aggregate, effectively free. Scale the check log to 500M rows across a nightly suite and the shape still holds: one pass, group in memory, no join. The only real cost is the LOWER() call defeating any plain index on severity, but since you are already filtering on passed and aggregating over the whole failing set, a full scan is the plan you want anyway. A functional index on LOWER(severity) only pays off if you later filter to a single severity level. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where-the-data-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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.