Double Take

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: L3
Asked in: Google

Problem

A downstream team flagged that the data quality checks table has rows repeating the same table-and-column combination. Surface every pair that appears more than once along with how many times it shows up, worst offenders first. Include each pair's standing among the offenders and its share of all the duplicated records.

Passed QA twice. That's the problem.

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 5 of 7 rows
tbl_name	col_name	duplicate_count	dup_rank	pct_of_dups
products	amount	30	1	15
users	email	30	1	15
ad_impressions	region	28	3	14
events	created_at	28	3	14
orders	user_id	28	3	14

Worked solution and explanation

The trap hiding in a data quality problem

This is a GROUP BY / HAVING duplicate hunt wearing a data-quality badge. The real question being probed: do you know that a duplicate here is defined by exactly two columns, tbl_name and col_name, and nothing else? Anyone can write COUNT(*) with a HAVING filter. The tell is what you put in the GROUP BY. Drop in rule or run_at because they feel like part of the row and every group collapses to size one, so you report zero duplicates and confidently hand back an empty result to a team that can see the repeats with their own eyes.

Break down the requirements

Step 1: Isolate the duplicate key

Isolate the duplicates: GROUP BY tbl_name, col_name and keep only the groups with COUNT(*) > 1, capturing the duplicate_count for each offending table-and-column pair. Materialize this as the pair_counts CTE so the analytic step operates on the filtered duplicate set only.

Step 2: Rank the worst offenders

Rank the offenders: apply RANK() OVER (ORDER BY duplicate_count DESC) so the worst-duplicated pairs surface first and ties share a position, telling the downstream team which combinations to triage initially.

Step 3: Quantify each pair's share

Quantify the burden share: divide each pair's duplicate_count by SUM(duplicate_count) OVER () (the total across all duplicate pairs) and ROUND to 2 decimals to express each pair's percentage of the overall duplicate volume.

The solution

GROUP BY with HAVING for duplicate detection

WITH pair_counts AS (
    SELECT tbl_name, col_name, COUNT(*) AS duplicate_count
    FROM dq_checks
    GROUP BY tbl_name, col_name
    HAVING COUNT(*) > 1
)
SELECT
    tbl_name,
    col_name,
    duplicate_count,
    RANK() OVER (ORDER BY duplicate_count DESC) AS dup_rank,
    ROUND(100.0 * duplicate_count / SUM(duplicate_count) OVER (), 2) AS pct_of_dups
FROM pair_counts
ORDER BY duplicate_count DESC, tbl_name, col_name

Common follow-up questions

How would you keep one record per duplicate group and delete the rest? (Tests `ROW_NUMBER`-based deduplication or DELETE with a CTE.)
What if the duplicate definition also had to include the rule column? (Tests adjusting the GROUP BY key based on business requirements.)
How would you prevent these duplicates from landing in the first place? (Tests UNIQUE constraints and upsert patterns.)