Finding Duplicates & Data Quality SQL Topic exerciseHardVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Compare Non-Survivors with the Approved Record

Use the approved precedence to return each non-survivor, its survivor_record_id, and NULL-safe region_changed, status_changed, and phone_changed flags.

  • Window functions
  • Subqueries
  • Aggregation
  • HAVING
  • CASE expressions

Exercise brief

Understand the request

Data remediation engineer Before any merge, reviewers need to know which losing records differ from the approved survivor and which are exact retries.

Before any merge, reviewers need to know which losing records differ from the approved survivor and which are exact retries. Use the approved precedence to return each non-survivor, its survivor_record_id, and NULL-safe region_changed, status_changed, and phone_changed flags.

Return

  • Return customer_key, record_id, survivor_record_id, region_changed, status_changed, and phone_changed.
  • Order by customer_key, then record_id.

Constraints

  • Use ROW_NUMBER and FIRST_VALUE over the same deterministic window order.
  • Treat two NULL values as equal and NULL versus non-NULL as changed.
  • Write a SELECT preview only; do not execute DELETE or UPDATE.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

quality_records

  • record_idINTEGER
  • customer_keyTEXT
  • regionTEXT
  • statusTEXT
  • phoneTEXT
  • updated_atTEXT
  • ingested_atTEXT

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

FIRST_VALUE can carry the winner’s ID and governed attributes onto every row in the duplicate partition.

Hint 2

Use the exact same updated_at, ingested_at, and record_id ordering for ROW_NUMBER and every FIRST_VALUE.

Hint 3

For each field, return unchanged only for equal values or when both values are NULL.

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, region, status, phone, ROW_NUMBER() OVER (PARTITION BY customer_key ORDER BY updated_at DESC, ingested_at DESC, record_id DESC) AS survivor_rank, FIRST_VALUE(record_id) OVER (PARTITION BY customer_key ORDER BY updated_at DESC, ingested_at DESC, record_id DESC) AS survivor_record_id, FIRST_VALUE(region) OVER (PARTITION BY customer_key ORDER BY updated_at DESC, ingested_at DESC, record_id DESC) AS survivor_region, FIRST_VALUE(status) OVER (PARTITION BY customer_key ORDER BY updated_at DESC, ingested_at DESC, record_id DESC) AS survivor_status, FIRST_VALUE(phone) OVER (PARTITION BY customer_key ORDER BY updated_at DESC, ingested_at DESC, record_id DESC) AS survivor_phone 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, survivor_record_id, CASE WHEN region = survivor_region OR (region IS NULL AND survivor_region IS NULL) THEN 0 ELSE 1 END AS region_changed, CASE WHEN status = survivor_status OR (status IS NULL AND survivor_status IS NULL) THEN 0 ELSE 1 END AS status_changed, CASE WHEN phone = survivor_phone OR (phone IS NULL AND survivor_phone IS NULL) THEN 0 ELSE 1 END AS phone_changed FROM ranked WHERE survivor_rank > 1 ORDER BY customer_key, record_id;

Why this works

A remediation preview should explain impact, not merely list losing IDs. Window functions attach the approved record to each candidate, while explicit NULL-safe comparisons distinguish exact retries from records that require attribute-level review.

Success check

Four non-survivors are compared with the correct winner; the identical C500 retry has zero change flags while real conflicts are exposed.

Expected result

Use this output to verify values, aliases, ordering, and row count.

customer_keyrecord_idsurvivor_record_idregion_changedstatus_changedphone_changed
C10010011003010
C10010021003011
C20010041005100
C50010091010000

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.