Duplicate Data Management — Learn Window Functions, Set Operations, and CTEs in Practice

QUESTION 6

Get each group’s latest row with DISTINCT ON — From combination deduplication to representative-row selection

DISTINCT ONPostgreSQLRepresentative rowLatest row

Background ▼

In Basic Q6, multi-column DISTINCT returned the distinct combinations of (user_id, category). In practice, we often need the entire representative row for each group—for example, each user’s latest purchase, including all its other columns. PostgreSQL’s DISTINCT ON solves this elegantly in a single query.

SELECT DISTINCT ON (user_id)  -- keep only the first row for each user_id
  user_id, order_id, purchased_at
FROM purchase_logs
ORDER BY user_id, purchased_at DESC;  -- the sort order determines the first row

ORDER BY determines which row to keep:DISTINCT ON keeps the row that comes first in each group after sorting. Therefore, the leading expressions in ORDER BY must match the DISTINCT ON columns, followed by an ordering that puts the row to retain first (DESC for the latest date).

Problem ▼

The purchase_logs table stores each user’s purchase history over time. Retrieve exactly one row per user: the latest purchase row, including order_id, category, and amount.

Table used

▸ purchase_logs

order_id	user_id	category	amount	purchased_at
1	101	Books	1500	2026-01-10
2	102	Food	800	2026-01-12
3	101	Electronics	3000	2026-02-05
4	103	Books	1200	2026-02-08
5	102	Electronics	5000	2026-03-01
6	101	Food	600	2026-03-15
7	103	Food	900	2026-02-20

Note: user_id=101 has 3 purchases, while 102 and 103 have 2 each. Keep only the row with the newest purchased_at (101 → order_id=6, 102 → 5, 103 → 7). Output in ascending user_id order.

Expected Output ▼

user_id	order_id	category	amount	purchased_at
101	6	Food	600	2026-03-15
102	5	Electronics	5000	2026-03-01
103	7	Food	900	2026-02-20

Model Answer ▼

SELECT DISTINCT ON (user_id)  -- keep the first row for each user_id
  user_id,
  order_id,
  category,
  amount,
  purchased_at
FROM purchase_logs
ORDER BY user_id, purchased_at DESC;  -- sort so the latest row comes first

/*
  Execution order:
  1. FROM purchase_logs                   → read the rows
  2. ORDER BY user_id, purchased_at DESC  → sort so the latest comes first
  3. DISTINCT ON (user_id)                → keep the first row for each user
  4. SELECT 5 columns                     → output the entire latest-purchase row
  */

Explanation (table transitions & key points) ▼

SELECT DISTINCT ON (user_id)
  user_id, order_id, category,
  amount, purchased_at
FROM purchase_logs
ORDER BY user_id,
         purchased_at DESC;

LEGEND ▼

Rows read / loaded

① Source data ▼

FROM purchase_logsRead the 7 rows of purchase_logs. Each user_id has multiple purchase rows; the goal is to keep only the one with the newest purchased_at.

1 / 4

order_id	user_id	category	amount	purchased_at
1	101	Books	1500	2026-01-10
2	102	Food	800	2026-01-12
3	101	Electronics	3000	2026-02-05
4	103	Books	1200	2026-02-08
5	102	Electronics	5000	2026-03-01
6	101	Food	600	2026-03-15
7	103	Food	900	2026-02-20

purchase_logs: 7 rows (multiple purchase histories per user_id)

LEARNING POINTS

DISTINCT ON — GROUP REPRESENTATIVE ROW

DISTINCT ON — Select the entire representative row according to sort order

Multi-column DISTINCT gives unique combinations; DISTINCT ON gives one representative row per group
ORDER BY user_id, purchased_at DESC → first row for each user_id = latest row

DISTINCT ON works in two stages: sort → keep the first: DISTINCT ON (user_id) scans rows in ORDER BY order and keeps only the first row where each user_id appears. In other words, DISTINCT ON decides what defines a group, while ORDER BY decides which row wins. Use purchased_at DESC to keep the latest or ASC to keep the earliest; changing the secondary sort key changes the selection rule.

The decisive difference from GROUP BY + MAX — it returns the whole row: SELECT user_id, MAX(purchased_at) FROM purchase_logs GROUP BY user_id returns only the latest date; retrieving order_id or amount from that row requires a self-join. DISTINCT ON retrieves all columns of the representative row in one query. The standard-SQL equivalent is ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchased_at DESC) + rn = 1, whose deletion variant appears in Q10.

Make ties deterministic with a tie-breaker: If one user has two purchases at the same time, which one is first is undefined. Add a unique key such as order_id DESC at the end of ORDER BY so the same row is selected every time. Reproducibility is a basic quality requirement for production SQL.

ANTI-PATTERNS

The first ORDER BY expression does not match the DISTINCT ON column: SELECT DISTINCT ON (user_id) ... ORDER BY purchased_at DESC causes PostgreSQL to return “SELECT DISTINCT ON expressions must match initial ORDER BY expressions.” Always start ORDER BY with the DISTINCT ON columns, then add the selection-priority columns.

Relying on DISTINCT ON without considering portability: DISTINCT ON is a PostgreSQL-specific extension and is not available in MySQL or SQL Server. For products spanning multiple databases, the standard-SQL ROW_NUMBER() + subquery (rn = 1) pattern is safer. A practical split is DISTINCT ON for internal analysis and window functions for SQL embedded in applications.

PRACTICAL COLUMN

“Latest one row per group” is one of the most common practical SQL patterns. Examples include retrieving each customer’s latest order (a CRM dashboard), the latest measurement for each sensor (IoT monitoring), and each employee’s latest job-title record (restoring current values from a history table). In an era where immutable history logs are standard, this pattern for restoring a current snapshot from history is worth practicing until it becomes second nature.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 7

Detect count differences with EXCEPT ALL — Reconcile inventory by subtracting multisets

EXCEPT ALLMultisetCount differenceInventory reconciliation

Background ▼

Basic Q7 used EXCEPT to return a set difference, but duplicates are removed internally, so a quantity difference such as “three on the left and two on the right” disappears. EXCEPT ALL preserves duplicates and cancels exactly one matching row for each matching value, performing multiset subtraction.

-- A = {x, x, x, y} / B = {x, y}
A EXCEPT B      -- → {}        both x and y exist in B → all removed
A EXCEPT ALL B  -- → {x, x}    two x remain: 3 − 1 = 2

“Does it exist?” and “How many are missing?” are different questions: If you use EXCEPT for inventory or ledger reconciliation where you need to detect a quantity difference, a difference of one and a difference of one hundred are both collapsed to one value. Choose the ALL form whenever quantity matters.

Problem ▼

Reconcile book inventory system_stock (one row per item) with the physical count counted_stock. Retrieve the inventory present in the books but missing from the physical count, with one result row for each missing item.

Tables used

▸ system_stock (book inventory)

stock_id	sku
1	A001
2	A001
3	A001
4	B002
5	B002
6	C003
7	D004

▸ counted_stock (physical count)

scan_id	sku
1	A001
2	A001
3	B002
4	B002
5	C003

Book inventory is A001×3, B002×2, C003×1, D004×1; the physical count is A001×2, B002×2, C003×1. A001 is short by 1 and D004 by 1, so the result has 2 rows. Output in ascending sku order.

Expected Output ▼

sku
A001
D004

Model Answer ▼

SELECT sku FROM system_stock
EXCEPT ALL                     -- subtract right from left (preserve duplicates)
SELECT sku FROM counted_stock
ORDER BY sku;

/*
  Execution order:
  1. Left SELECT: read all 7 sku rows from system_stock (preserve duplicates)
  2. Right SELECT: read all 5 sku rows from counted_stock (preserve duplicates)
  3. EXCEPT ALL: subtract the multisets
  4. ORDER BY sku
  */

Explanation (table transitions & key points) ▼

SELECT sku FROM system_stock
EXCEPT ALL
SELECT sku FROM counted_stock
ORDER BY sku;

LEGEND ▼

Rows read / loaded

① Left SELECT — Book inventory ▼

SELECT sku FROM system_stockSelect sku from the book inventory. Because one item is one row, three A001 rows mean three A001 items in the books. With EXCEPT ALL, these duplicates retain their meaning as quantities.

1 / 4

sku
A001
A001
A001
B002
B002
C003
D004

system_stock: 7 rows (A001×3, B002×2, C003×1, D004×1)

LEARNING POINTS

MULTISET DIFFERENCE

EXCEPT ALL — Set difference with counts (multiset subtraction)

A set operation for detecting quantity shortages and surpluses, not just value presence
Book inventory (7) EXCEPT ALL physical count (5) = 2 missing rows

EXCEPT and EXCEPT ALL answer different questions: EXCEPT asks “which values exist on the left but not even once on the right?” EXCEPT ALL asks “which left-side rows cannot be fully cancelled by the right side?” Without ALL here, A001 would disappear because that value exists in the physical count, leaving only D004. Use ALL for quantity discrepancies and the plain form for presence differences.

Think in per-value counts: EXCEPT ALL is equivalent to calculating “left count − right count, with negative results treated as zero” for each value. Here: A001: 3−2=1, B002: 2−2=0, C003: 1−1=0, D004: 1−0=1. This count model unifies UNION ALL (addition), EXCEPT ALL (subtraction), and INTERSECT ALL (the minimum).

Reverse direction means excess inventory: Swapping the sides detects inventory found physically but absent from the books—excess or unrecorded stock. In practice, it is standard to connect both directions with UNION ALL and list shortages and excesses in one query.

ANTI-PATTERNS

Using EXCEPT without ALL for quantity reconciliation: This is a common production bug. A report says “no discrepancy” even though one A001 is missing because EXCEPT silently removes duplicates. For comparing tables where duplicate rows carry meaning—detail rows, logs, or scan records—always consider the ALL form. Plain EXCEPT is sufficient for master-data presence comparisons.

Reporting without preserving which item is short by how many: EXCEPT ALL returns one row per shortage, which is not the easiest report format. For reporting, wrap it and aggregate, for example SELECT sku, COUNT(*) AS shortage FROM (…EXCEPT ALL…) t GROUP BY sku, to produce “item × shortage count.” Offering both raw discrepancy rows and a summary is friendlier to readers.

PRACTICAL COLUMN

Multiset differences are useful for every reconciliation where counts matter: book inventory versus physical stock, payment logs versus settlement details (even when equal-value transactions repeat), and row-level validation before and after a data migration. In migration testing, the golden rule is to confirm that EXCEPT ALL is empty in both directions; that mechanically detects both missing rows and unexpected duplicates.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 8

Retrieve every duplicate row in one pass with COUNT(*) OVER — Evolve subquery + IN with a window function

Window functionsCOUNT() OVERPARTITION BYAll duplicate rows

Background ▼

Basic Q8 used a two-scan pattern: a GROUP BY + HAVING subquery followed by an IN lookup against the original table. With the window function COUNT(*) OVER (PARTITION BY …), we can attach “the number of rows with my key” to every row without collapsing the rows, obtaining the same result plus the duplicate count in one table read.

COUNT(*) OVER (PARTITION BY member_id, class_id)
-- Unlike GROUP BY, rows are not reduced; each row receives its group count

A window function cannot be written directly in WHERE: Window functions are calculated in the SELECT phase, after WHERE. Therefore WHERE COUNT(*) OVER (…) > 1 is an error. The standard pattern is to calculate the value in a subquery (or CTE), then filter it in the outer query.

Problem ▼

The fitness club’s reservations table contains duplicate bookings caused by a system problem. Retrieve every reservation row whose (member_id, class_id) pair is duplicated, together with the duplicate count (dup_cnt).

Table used

▸ reservations

reservation_id	member_id	class_id	reserved_at
1	201	Y01	2026-01-05
2	202	P02	2026-01-06
3	201	Y01	2026-01-07
4	203	Y01	2026-01-08
5	202	P02	2026-01-09
6	202	S03	2026-01-10

The duplicate pairs are (201, Y01) and (202, P02), with 2 rows each and 4 result rows total. Output in ascending member_id, class_id, reservation_id order.

Expected Output ▼

reservation_id	member_id	class_id	reserved_at	dup_cnt
1	201	Y01	2026-01-05	2
3	201	Y01	2026-01-07	2
2	202	P02	2026-01-06	2
5	202	P02	2026-01-09	2

Model Answer ▼

SELECT
  reservation_id, member_id, class_id, reserved_at, dup_cnt
FROM (
  SELECT
    r.*,
    COUNT(*) OVER (
      PARTITION BY member_id, class_id  -- count rows in each pair
    ) AS dup_cnt
  FROM reservations r
) t
WHERE dup_cnt > 1
ORDER BY member_id, class_id, reservation_id;

/*
  Execution order:
  1. Inner FROM reservations  → read all 6 rows
  2. COUNT(*) OVER (PARTITION BY member_id, class_id)
  3. Outer WHERE dup_cnt > 1  → keep rows in duplicate groups
  4. SELECT 5 columns
  5. ORDER BY member_id, class_id, reservation_id
  */

Explanation (table transitions & key points) ▼

SELECT reservation_id, member_id,
  class_id, reserved_at, dup_cnt
FROM (
  SELECT r.*,
    COUNT(*) OVER (
      PARTITION BY member_id,
                   class_id
    ) AS dup_cnt
  FROM reservations r
) t
WHERE dup_cnt > 1
ORDER BY member_id, class_id,
         reservation_id;

LEGEND ▼

Rows read / loaded

① Source data ▼

FROM reservations rRead the 6 rows of reservations. The pairs (201, Y01) and (202, P02) each appear twice, which makes them duplicate bookings.

1 / 4

reservation_id	member_id	class_id	reserved_at
1	201	Y01	2026-01-05
2	202	P02	2026-01-06
3	201	Y01	2026-01-07
4	203	Y01	2026-01-08
5	202	P02	2026-01-09
6	202	S03	2026-01-10

reservations: 6 rows (duplicate (member_id, class_id) pairs)

LEARNING POINTS

WINDOW COUNT — ONE-PASS DUPLICATE DETECTION

COUNT(*) OVER — Add the number of rows in the same group without collapsing rows

GROUP BY reduces rows; a window function keeps rows and appends an aggregate value
6 rows → 6 rows + dup_cnt column → WHERE dup_cnt > 1 → 4 rows

Compared with GROUP BY: “collapse” versus “append”: GROUP BY collapses rows with the same key to one row, so detail such as reservation_id and reserved_at disappears. A window function appends the aggregate while preserving every row, exactly fitting an investigation that needs both details and counts. The subquery + IN in Basic Q8 was needed to recover details that GROUP BY had collapsed.

One pass gives both performance and readability: The subquery + IN pattern references the table twice, while the window-function version completes with one read plus partitioning. The difference becomes more noticeable on large tables, and dup_cnt adds useful information to the result. It is worth remembering as a first-choice duplicate-detection pattern.

Calculate the value in a subquery, then filter it: In SQL’s logical evaluation order, WHERE is evaluated before window functions. To filter by a window result, materialize it as a column one level inside. The inline view (…) t here is equivalent to a CTE such as WITH t AS (…); Q10 combines that CTE form with DELETE.

ANTI-PATTERNS

Writing a window function directly in WHERE: WHERE COUNT(*) OVER (PARTITION BY …) > 1 raises “window functions are not allowed in WHERE.” This is easy to confuse with HAVING or the QUALIFY extension in Snowflake/BigQuery. In PostgreSQL, always insert a subquery or CTE.

Partitioning by the wrong columns and making every row look duplicated: If you use PARTITION BY member_id alone, all reservations by one member—including different classes—enter the same window and dup_cnt is inflated. State the duplicate definition first and put exactly those columns in PARTITION BY. The same discipline as choosing a deduplication key in Basic Q6 applies here.

PRACTICAL COLUMN

COUNT(*) OVER is a standard modern-SQL shape for duplicate investigations. Examples include duplicate booking or payment reports (details and counts on one screen), data-quality dashboards showing the distribution by dup_cnt, and ETL checks confirming that dup_cnt > 1 returns zero rows after loading. Replacing it with ROW_NUMBER() OVER reveals the ordinal position of each duplicate and leads directly to Q10’s deletion pattern.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 9

Chain INTERSECT to find users common to every period — Identify users active for three consecutive months

Chained INTERSECTSet operationsRetained usersCommon to N periods

Background ▼

Basic Q9 introduced INTERSECT as the common part of two sets. INTERSECT can be chained any number of times, building a filter for elements common to every set, like A ∩ B ∩ C. “Active for three consecutive months” in retention analysis is a representative use case.

SELECT user_id FROM logins WHERE login_month = '2026-01'
INTERSECT   -- January ∩ February
SELECT user_id FROM logins WHERE login_month = '2026-02'
INTERSECT   -- (January ∩ February) ∩ March
SELECT user_id FROM logins WHERE login_month = '2026-03';

INTERSECT checks “common” with duplicate removal: Multiple logins in one month still count as one set element. This is an existence test—“present at least once in each period”—not a count test. Use INTERSECT ALL or GROUP BY when login counts also matter.

Problem ▼

The logins table stores monthly login records, including duplicate logins within a month. Retrieve the user_id of every continuing user who logged in during all three months: January, February, and March 2026.

Table used

▸ logins

login_id	user_id	login_month
1	101	2026-01
2	102	2026-01
3	103	2026-01
4	101	2026-01
5	101	2026-02
6	103	2026-02
7	104	2026-02
8	101	2026-03
9	103	2026-03
10	105	2026-03
11	103	2026-03

January is {101,102,103}, February is {101,103,104}, and March is {101,103,105}, so the common users are 101 and 103. Output in ascending user_id order.

Expected Output ▼

user_id
101
103

Model Answer ▼

SELECT user_id FROM logins WHERE login_month = '2026-01'
INTERSECT                                                 -- users common to January and February
SELECT user_id FROM logins WHERE login_month = '2026-02'
INTERSECT                                                 -- also common to March
SELECT user_id FROM logins WHERE login_month = '2026-03'
ORDER BY user_id;

/*
  Execution order:
  1. Each SELECT          → extract the user set for one month
  2. First INTERSECT      → extract the common users
  3. Second INTERSECT     → retain users common to all three
  4. ORDER BY user_id     → sort and output
  */

Explanation (table transitions & key points) ▼

SELECT user_id FROM logins
  WHERE login_month = '2026-01'
INTERSECT
SELECT user_id FROM logins
  WHERE login_month = '2026-02'
INTERSECT
SELECT user_id FROM logins
  WHERE login_month = '2026-03'
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① Set 1 — January login users ▼

SELECT user_id FROM logins WHERE login_month = '2026-01'Extract January login rows. user_id=101 logged in twice (login_id=1, 4), but INTERSECT treats the set as one element, giving {101, 102, 103}.

1 / 5

login_id	user_id	login_month
1	101	2026-01
2	102	2026-01
3	103	2026-01
4	101	2026-01 (duplicate)

January: 4 rows → set {101, 102, 103}

LEARNING POINTS

CHAINED INTERSECTION

Chained INTERSECT — Keep only elements common to every set

A declarative retention filter whose conditions accumulate as AND at each stage
{101,102,103} ∩ {101,103,104} ∩ {101,103,105} = {101,103}

Chains are evaluated step by step from the left: INTERSECT operations at the same precedence are left-associative, so the intermediate result (January ∩ February) is intersected with March. The common set shrinks monotonically at each stage, making it easy to see when a user drops out for both learning and debugging. When mixing UNION with INTERSECT, remember that INTERSECT has higher precedence than UNION; use parentheses to make intent explicit.

Equivalent to HAVING COUNT(DISTINCT), with a different use case: The same result can be written as

SELECT user_id FROM logins WHERE login_month IN ('2026-01','2026-02','2026-03') GROUP BY user_id HAVING COUNT(DISTINCT login_month) = 3

. Use HAVING when the number of periods is variable or parameterized; use INTERSECT when a fixed “present in every period” requirement is clearer in the syntax. COUNT must be DISTINCT, or duplicate logins within one month inflate the count.

The set-operation map: UNION / EXCEPT / INTERSECT: Across the basic and advanced sets, UNION means “exists in any set” (OR), EXCEPT means “exists only on the left” (difference), and INTERSECT means “exists in every set” (AND). In retention analysis, current month EXCEPT previous month is new, previous month EXCEPT current month is churn, and previous month INTERSECT current month is retained—the three operations map directly to user segments.

ANTI-PATTERNS

Confusing “three logins in three months” with “three logins total”: GROUP BY user_id HAVING COUNT(*) >= 3 means at least three total logins, so a user who logged in three times in January alone passes. “At least once in each period” and “at least N times in total” are different requirements. Chained INTERSECT (or COUNT(DISTINCT login_month) = 3) expresses the former correctly.

Mismatched column counts/types or an intermediate ORDER BY: Like UNION and EXCEPT, INTERSECT requires every SELECT to have compatible column counts and types. Individual SELECTs cannot have their own ORDER BY; sort once at the end of the whole set expression. This is easy to forget when chaining three or more sets.

PRACTICAL COLUMN

Finding users common to N periods is central to retention and continuity analysis. Examples include customers who paid in three consecutive months, customers who traded in every quarter, and configuration keys present in every environment (dev/stg/prod) during release validation. As the number of periods grows, HAVING COUNT(DISTINCT) or a window-function continuity check is usually more practical than listing many INTERSECT clauses. A useful two-stage approach is to validate the requirement with INTERSECT first, then rewrite the operational query as an aggregate.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 10

Physically delete duplicates with ROW_NUMBER + DELETE — The complete latest-row cleansing pattern

ROW_NUMBERCTE + DELETEDuplicate deletionRETURNING

Background ▼

Basic Q10 used MIN(id) + GROUP BY to choose the row to keep. The advanced finale is the practical cleansing procedure: number deletion candidates with ROW_NUMBER() and physically delete them. It combines three points: keep the latest row, make the tie-break deterministic, and record deleted rows with RETURNING.

ROW_NUMBER() OVER (
  PARTITION BY email                      -- duplicate group
  ORDER BY created_at DESC, contact_id DESC  -- make the row to keep rn=1
) AS rn   -- keep rn=1 and delete rn>1

DELETE is irreversible—always use this three-part safety set: ① preview the targets with a SELECT using the same condition, ② execute inside a transaction and check the count before COMMIT, and ③ record deleted rows with RETURNING. Skipping this procedure is a classic cause of duplicate-cleanup incidents.

Problem ▼

The customer master contacts contains duplicate registrations for the same email. Keep the newest row for each email (the maximum created_at) and DELETE the older duplicate rows. Return the deleted contact_id, email, and created_at with RETURNING.

Table used

▸ contacts

contact_id	email	name	created_at
1	sato@ex.com	Sato	2026-01-10
2	suzuki@ex.com	Suzuki	2026-01-12
3	sato@ex.com	Sato	2026-02-01
4	tanaka@ex.com	Tanaka	2026-02-05
5	sato@ex.com	Sato	2026-03-01
6	suzuki@ex.com	Suzuki	2026-02-20

sato@ex.com has 3 rows (latest contact_id=5), suzuki@ex.com has 2 (latest 6), and tanaka@ex.com has 1. The rows deleted are contact_id = 1, 2, and 3.

Expected Output ▼

contact_id	email	created_at
1	sato@ex.com	2026-01-10
2	suzuki@ex.com	2026-01-12
3	sato@ex.com	2026-02-01

Model Answer ▼

WITH ranked AS (
  SELECT
    contact_id,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC, contact_id DESC  -- latest is rn=1; ID breaks ties
    ) AS rn
  FROM contacts
)
DELETE FROM contacts
WHERE contact_id IN (
  SELECT contact_id FROM ranked WHERE rn > 1
)
RETURNING contact_id, email, created_at;

/*
  Execution order:
  1. CTE ranked  → number rows newest-first within each email
  2. Subquery    → select deletion targets where rn > 1
  3. DELETE      → physically delete the targets
  4. RETURNING   → return the deleted rows as the result
  */

Explanation (table transitions & key points) ▼

WITH ranked AS (
  SELECT contact_id,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC,
               contact_id DESC
    ) AS rn
  FROM contacts
)
DELETE FROM contacts
WHERE contact_id IN (
  SELECT contact_id FROM ranked
  WHERE rn > 1
)
RETURNING contact_id, email,
          created_at;

LEGEND ▼

Rows read / loaded

① Source data ▼

FROM contactsThe contacts table has 6 rows. sato@ex.com appears 3 times and suzuki@ex.com twice. The goal is to keep only the row with the greatest created_at for each email (5, 6, and 4).

1 / 5

contact_id	email	name	created_at
1	sato@ex.com	Sato	2026-01-10
2	suzuki@ex.com	Suzuki	2026-01-12
3	sato@ex.com	Sato	2026-02-01
4	tanaka@ex.com	Tanaka	2026-02-05
5	sato@ex.com	Sato	2026-03-01
6	suzuki@ex.com	Suzuki	2026-02-20

contacts: 6 rows (sato×3, suzuki×2, tanaka×1)

LEARNING POINTS

DEDUP DELETE — KEEP THE LATEST

ROW_NUMBER + DELETE — Physically delete old duplicates while keeping the latest

Number rows (rn) → select deletion targets (rn>1) → DELETE → RETURNING for an audit trail
6 rows → DELETE {1,2,3} → 3 rows with unique emails

Evolving the “choose the row to keep” idea—from MIN(id) to ROW_NUMBER: Basic Q10’s MIN(contact_id) can express only “keep the smallest ID,” usually the oldest, and breaks when IDs are not chronological. ROW_NUMBER’s ORDER BY can define the winner by any rule. Use created_at DESC for the latest or sort by completeness to keep the row with the most information; the requirement becomes the ordering itself.

A tie-breaker guarantees deterministic deletion: With two rows sharing an email and created_at, ORDER BY created_at DESC alone may assign rn=1 differently between executions. Adding contact_id DESC makes the order unique, so the same row remains every time. This is essential in production work that must be reproducible and reconciled with rehearsals.

RETURNING is evidence for an irreversible operation: RETURNING can be attached to DELETE, UPDATE, and INSERT to return affected rows immediately in PostgreSQL. The application can write deleted rows to an audit log without first issuing INSERT INTO contacts_archive SELECT …. Further, WITH deleted AS (DELETE … RETURNING *) INSERT INTO archive SELECT * FROM deleted completes deletion and archiving in one statement and one transaction.

ANTI-PATTERNS

Deleting immediately without previewing: This is the most dangerous anti-pattern. Replace DELETE FROM contacts with SELECT * FROM contacts to inspect targets first, and in production use BEGIN; → DELETE (check count) → COMMIT/ROLLBACK;. The count check before COMMIT is the last defense against “three expected rows” becoming 30,000.

Deleting duplicates without preventing recurrence: If you do not add a unique constraint such as CREATE UNIQUE INDEX ON contacts (email) after cleansing, duplicates will return and cleanup becomes recurring work. Change the application to INSERT … ON CONFLICT (email) DO UPDATE (UPSERT) so the complete duplicate-management solution prevents duplicates at the entry point.

PRACTICAL COLUMN

This is the culmination of Q1–Q10. Practical duplicate cleansing follows a workflow: detect (Q3/Q8: GROUP BY + HAVING, COUNT() OVER) → investigate (retrieve duplicate details and analyze causes) → choose representatives (Q4/Q10: MIN, DISTINCT ON, ROW_NUMBER) → delete and record (DELETE + RETURNING) → prevent recurrence (unique constraint + UPSERT). Examples include customer-master matching, repairing duplicated ETL loads, and removing duplicate events from a log platform. Keeping this five-stage map in mind makes duplicate handling safer and reproducible in any environment.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.