DQ Score Spread

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: L4
Asked in: Box

Problem

How far apart are the best and worst monitored tables in the dq_checks audit log? The analyst reviewing it scores each table by adding up the fail percentages of all of its checks.

The spread in data quality scores.

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

dq_checks: 10 of 200 rows
check_id	tbl_name	col_name	rule	passed	fail_pct	run_at	severity
131	users	email	unique	1	NULL	2026-02-02 01:00:00	high
162	products	amount	range	1	NULL	2026-03-03 02:00:00	medium
193	transactions	status	format	1	NULL	2026-04-04 03:00:00	low
224	events	created_at	referential	1	NULL	2026-05-05 04:00:00	Critical
255	sessions	price	freshness	0	15	2026-06-06 05:00:00	HIGH
286	ad_impressions	region	Not_Null	1	NULL	2026-07-07 06:00:00	critical
317	orders	user_id	UNIQUE	1	0	2026-08-08 07:00:00	high
348	users	email	not_null	1	NULL	2026-09-09 08:00:00	medium
379	products	amount	unique	1	NULL	2026-01-10 09:00:00	low
410	transactions	status	range	0	30	2026-02-11 10:00:00	Critical

Expected output

First 1 of 1 rows
dq_spread
450

Worked solution and explanation

What this really is

This is a spread of group totals dressed up as a data-quality report. The figure being compared is not a column of dq_checks. It is a sum per tbl_name, so the answer is the extremes of an aggregate. Anyone can write SUM(fail_pct). What separates candidates is noticing that the highest and lowest are taken over table totals, not over checks. Skip that step and MAX(fail_pct) - MIN(fail_pct) returns 95, the range of single checks, instead of 450, the gap between 'events' at 480 and 'orders' at 30.

Walking to the answer

Step 1: Reduce the checks to one total per table

GROUP BY tbl_name with SUM(fail_pct) turns the 200 rows of dq_checks into seven table totals. SUM skips the NULL fail_pct on passing checks, which is exactly the business rule: a check that passed adds nothing.

Step 2: Take the extremes over the totals

Wrap that result in a derived table and compute MAX(total_fail) - MIN(total_fail) over its seven rows. The outer level has no GROUP BY because the whole set of tables is one group, so the answer comes back as a single dq_spread row.

Totals per table, then the gap between the extremes

SELECT MAX(total_fail) - MIN(total_fail) AS dq_spread
FROM (
    SELECT tbl_name, SUM(fail_pct) AS total_fail
    FROM dq_checks
    GROUP BY tbl_name
) t

Spread over checks: SELECT MAX(fail_pct) - MIN(fail_pct) FROM dq_checks treats every check as a peer. On this data it returns 95: one check failing at 95 percent against one at 0.
Spread over tables: The derived table sums each tbl_name first, so 'events' carries 480 and 'orders' carries 30. The outer MAX and MIN then return 450, the gap the report is actually about.

Common follow-up questions

How would you also return which tables sit at the highest and lowest totals? (Tests keeping the per-table result and reading names off the extremes, including ties at either end.)
A table whose checks all passed has a `NULL` total. Should it count as the lowest? (Tests knowing that `SUM` over only `NULL` returns `NULL`, and that `MIN` then silently drops that table.)
How would you compute the spread separately for each `severity`? (Tests carrying a dimension through both levels, after normalizing the mixed casing in `severity`.)
If checks re-run daily and only the latest run of each rule should count, what changes? (Tests deduplicating to one row per table, column and rule before totaling.)