Select a Deterministic Survivor
Rank duplicate groups by updated_at, ingested_at, and record_id descending, then return each survivor ordered by customer_key.
- Window functions
- Subqueries
- Aggregation
- HAVING
- Filtering
Exercise brief
Understand the request
Master-data platform engineer A golden-record preview must keep the latest version of each duplicated customer key and remain stable when timestamps tie.
A golden-record preview must keep the latest version of each duplicated customer key and remain stable when timestamps tie. Rank duplicate groups by updated_at, ingested_at, and record_id descending, then return each survivor ordered by customer_key.
Return
- Return customer_key, record_id, updated_at, and survivor_rank.
- Order by customer_key.
Constraints
- Use ROW_NUMBER partitioned by customer_key.
- Use record_id as the final unique tie-breaker after both timestamps.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
quality_records
customer_keyTEXTrecord_idINTEGERupdated_atTEXTingested_atTEXT
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
ROW_NUMBER turns the business precedence policy into one winner per partition.
Hint 2
Timestamp ties require another stable ordering column.
Hint 3
Partition by customer_key and order by both timestamps plus record_id descending.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH ranked AS (SELECT customer_key, record_id, updated_at, ROW_NUMBER() OVER (PARTITION BY customer_key ORDER BY updated_at DESC, ingested_at DESC, record_id DESC) AS survivor_rank FROM quality_records WHERE customer_key IN (SELECT customer_key FROM quality_records WHERE customer_key IS NOT NULL GROUP BY customer_key HAVING COUNT(*) > 1)) SELECT customer_key, record_id, updated_at, survivor_rank FROM ranked WHERE survivor_rank = 1 ORDER BY customer_key;Why this works
A total ordering is essential for reproducible deduplication. The unique record_id tie-breaker prevents the engine from choosing arbitrary winners when timestamps match.
Success check
One reproducible survivor is selected for each duplicate group, including tied C100 and C500 timestamps.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| customer_key | record_id | updated_at | survivor_rank |
|---|---|---|---|
| C100 | 1003 | 2026-02-03 10:00:00 | 1 |
| C200 | 1005 | 2026-02-04 11:00:00 | 1 |
| C500 | 1010 | 2026-02-05 15:00:00 | 1 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
CASE Statements & Conditional Logic
Build NULL-aware classifications, precedence-safe decisions, flags, scores, and guarded calculations with portable CASE expressions.
Open the interactive workspace and practice across SQL topics.