# Double Take

> Passed QA twice. That's the problem.

Canonical URL: <https://datadriven.io/problems/double_take>

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.

## Tables

### dq_checks

- check_id INTEGER
- tbl_name TEXT
- col_name TEXT
- rule TEXT
- passed INTEGER
- fail_pct REAL
- run_at TEXT
- severity TEXT

Sample rows (10 of 200):

| 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**

```sql
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
```

> **Cost Analysis**
>
> Single pass over 800K rows with hash aggregation. A composite index on `(tbl_name, col_name)` enables an index-only scan, so the aggregate never touches the heap.

> **Interviewers Watch For**
>
> The key signal is whether the candidate locks the grain to `tbl_name, col_name` straight from the prompt, rather than sweeping in extra columns like `rule` or `run_at` that feel like part of the record.

> **Common Pitfall**
>
> Padding the GROUP BY with `rule` or `run_at` fragments the groups so every combination collapses to size one, the HAVING filter finds nothing, and you report zero duplicates on a table that is visibly full of them.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/double_take)
- [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.