Deduplication: Beginner

The question rarely contains the word key. It arrives as 'this feed double-writes sometimes, clean it up', as 'the daily order count runs 3% above what finance reports', or as 'every join off this table fans out'. All 3 describe the same table, and none says which columns make 2 rows the same. A row in orders has 5 columns, and 2 rows can agree on all 5, on 2 of them, or on 2 of them only after a string is trimmed, so the word duplicate means nothing until a column list goes with it.

Match on every column and no 2 of these rows are the same: 5 distinct rows, nothing to remove. If customer_id and order_date decide the match, rows 1001 to 1003 are copies of one key, which leaves 3 rows. On customer_id alone, customer 1's 4 rows fold into 1, which leaves 2. The same table gives a different answer under each definition, and the interviewer has handed you none of them. The first thing you produce is the definition, because every query after it is scored against that choice.

An exact duplicate agrees with another row in every column. It comes from a file loaded twice, a UNION ALL that should have been UNION, or a lookup table someone appended to instead of replacing. Nothing in the row tells the copies apart, so any copy can survive, and one keyword removes the rest. The question is answered in a line and the interviewer learns nothing from it.

A duplicate by key agrees in the columns that identify the entity and differs somewhere else. Rows 1001 to 1003 share a customer and a date, but order_id differs on each one, as do amount and created_at, because the feed wrote the order 3 times and stamped each write. Retries, change-data-capture replays after a failover, and corrections written as fresh rows all produce this shape. Removing these means deciding on a key and then on which copy survives, and almost every interview version of the question is this case, because it tests judgement rather than vocabulary.

The sentence worth saying out loud is the grain: what one row of the finished table stands for. 'I'll treat customer_id plus order_date as the key, so the result holds a single row for each customer on each date' takes 2 seconds for the interviewer to confirm or correct, and either response hands you the column list. If she answers that order_id is the business key, this table has no duplicates at all and the 3% overcount comes from a join downstream. That is a different problem, and you caught it before writing a line. Candidates who skip the sentence commit to a definition inside their SQL and discover it was the wrong one when the row count comes back.

Start with the case where the copies agree in every column. A customer file was assembled from 2 regional exports that overlapped, so imported_customers holds 6 rows for 3 customers. The ask is one copy of each row, and the whole answer is one keyword. Watch what the keyword actually compares, because the rows it keeps are the ones that trip people in the next question.

Of the 6 rows that go in, 4 come out. Customer 101's 2 rows agree on customer_id and email and carry the same segment, so they fold into 1, and so do customer 102's. Customer 103 keeps both rows, because one says retail and the other wholesale, and DISTINCT compares the entire selected row: a difference in any 1 of the 3 columns keeps a row alive. Drop segment from the select list and the same query returns 3 rows. The columns you list are the definition from the previous section, written into the query.

DISTINCT runs after the select list is computed, so it compares the expressions you wrote rather than the stored columns. Postgres compares case-sensitively by default, as do Snowflake and BigQuery, so there SELECT DISTINCT email keeps Ana@shopmail.com and ana@shopmail.com as 2 values; the MySQL and SQL Server defaults compare case-insensitively and fold them. SELECT DISTINCT LOWER(email) folds them on every engine, which is why the normalised expression is the one to write. The same rule makes SELECT DISTINCT * compare every stored column, including the ones that were never part of the key, which is the mistake the last section of this lesson takes apart.

The 4 rows are the ones DISTINCT returned, because both constructs do the same work: every column in the list becomes part of a group key, and rows with equal keys fold into 1. Most optimizers produce the same hash aggregate plan for either form, so no performance argument separates them on exact copies. What GROUP BY adds is the aggregate column: copies shows that 101 and 102 arrived twice and 103 once per segment, which is the first evidence of how the overlap happened. If the import had also stamped a loaded_at on each row, GROUP BY could carry MIN(loaded_at) beside each survivor, a column outside the key that DISTINCT has no way to express.

One constraint follows from that. Under DISTINCT, an ORDER BY column has to appear in the select list, because the engine holds no single value of an unselected column for a folded row. GROUP BY lifts the limit for aggregates, so ORDER BY MIN(loaded_at) is legal after a group and has no equivalent under DISTINCT. When the interviewer asks for the earliest copy's load time beside each surviving row, only the second form can produce it.

Pick the form that names the intent and say why: 'I'm using DISTINCT because it reads as deduplication; GROUP BY would read as aggregation, and I reach for it when I want a count beside each surviving row.' That sentence tells the interviewer you know the forms are equivalent and chose on purpose. Writing GROUP BY with no aggregate and no comment reads as not knowing DISTINCT exists, and writing UNION against a single source reads as an accident.

Both forms treat 2 NULLs in the same column as equal for grouping, even though NULL = NULL evaluates to UNKNOWN in a WHERE or a join. 2 rows with a NULL email and the same customer_id and segment fold into 1 under DISTINCT and under GROUP BY. That is the behaviour you want for a cleanup, and it deserves one sentence out loud, because the same 2 rows would refuse to match in a self-join written to find them. The interviewer hears that you know where NULL behaves one way and where it behaves the other.

The inner query folds orders to one row per key with its copy count; the outer one counts those rows and sums the copies, so total_rows is 5 and distinct_keys is 3 without a second scan of the table. extra_rows is 2: the dedup must delete 2 rows and return exactly 3, and if it returns 5 or 4 or 2 you know before the interviewer does. repeating_keys is 1, which says the problem is concentrated, a single key with 3 copies, rather than spread across the table. Postgres accepts COUNT(DISTINCT (customer_id, order_date)) as a one-line shortcut for the key count; most other engines reject the tuple, so the CTE form is the one to write when the dialect is unstated.

GROUP BY folds the 5 rows into 3 groups and counts each; HAVING then drops every group with a count of 1, leaving the key (1, 2024-01-01) with its count of 3. The filter has to be HAVING because WHERE runs before the fold and cannot see a count that does not exist yet; WHERE COUNT(*) > 1 is a syntax error on every engine and a common one under time pressure. Say the order as you write it: 'I group first, then filter on the count with HAVING, because WHERE cannot reference an aggregate.'

A window form shows the same rows without the join: COUNT(*) OVER (PARTITION BY customer_id, order_date) AS copies beside every column of orders, filtered to copies > 1 in an outer query. It reads shorter and introduces the partition clause the next section builds on; either form is accepted, and naming both marks you as someone who has done this more than once.

With the key agreed and the copies counted, the next mistake is a query that collapses the key alone. SELECT customer_id, order_date FROM orders GROUP BY customer_id, order_date returns the right 3 rows but has no order_id in them, and amount and created_at are gone as well. The interviewer asked for one row per key, and a row has 5 columns. The 3 columns that were never part of the key have to come from somewhere, and where they come from is the whole question.

The first instinct is to aggregate them. For this example, orders gains email as a sixth column, which the 10:00 write left NULL. Take the 4 rows dated 2024-01-01: customer 1's 3 writes, 100 at 09:00, 140 at 10:00 and 110 at 11:00, and customer 2's single write of 75 at 12:45. With one date in play, grouping by customer_id alone is the same as grouping by the agreed key. Wrap every non-key column in MAX and the query runs without complaint.

Customer 1 comes out at 11:00 with an amount of 140 and the email ana@shopmail.com. No source row carries that combination: the time belongs to the 11:00 write, the amount to the 10:00 write, and the email to whichever write had one, since MAX skips NULL and so hides that a write lost it. The latest write of this order says 110, and the revenue report built on this result will say 140. Aggregating column by column assembles a record from pieces of different rows, and the record it assembles never existed. This is what the question tests: whether you know that keeping one row per key means keeping a row, with its columns travelling together, rather than a key with the best value of each column stapled on.

PARTITION BY customer_id, order_date is the key from the first section written into the query: rows that share both columns form one partition and are numbered apart from every other partition. ORDER BY created_at DESC decides the numbering inside each one, newest first, so the 11:00 write of (1, 2024-01-01) gets rn 1 and the 09:00 write gets 3, with the 10:00 write between them at 2. Rows 1004 and 1005 sit alone in their partitions and get 1. The outer WHERE rn = 1 keeps 1003 along with those 2 singletons: 3 rows, the count the previous section said to expect, and row 1003 arrives whole, amount 110 beside its 11:00 timestamp. Change the 2 clauses and the same shape answers any key and any survivor rule.

WHERE is evaluated before window functions, so in the SELECT that defines rn the column does not exist yet, and WHERE rn = 1 fails with an unknown column error. The CTE computes rn first, and the outer query filters a result in which rn is an ordinary column. Say it as you type the WITH: 'I'm wrapping the ROW_NUMBER in a CTE because the outer WHERE can't filter on a window function in the same select.' Interviewers hear that sentence as evidence you have written this against a real engine, because the error it avoids is the one everybody hits the first time.

QUALIFY is to window functions what HAVING is to aggregates: a filter that runs after the function has been computed. Postgres offers DISTINCT ON (customer_id, order_date) with an ORDER BY customer_id, order_date, created_at DESC, which keeps the first row of each key in that order. Both are shortcuts for one dialect. The CTE form runs everywhere, so write it unless the interviewer has named the engine, and name the shortcut as you skip it: 'on Snowflake this is a QUALIFY, on Postgres a DISTINCT ON, and this form is portable.'

Return to the move most candidates make first. SELECT DISTINCT * FROM orders compares all 5 columns, and order_id differs on every row, so 5 rows go in and 5 come out. The interviewer sees the unchanged count and asks what the query did, and the answer is nothing. Narrowing the select list helps only until a non-key column differs: drop order_id and created_at and the 3 copies of (1, 2024-01-01) still survive, because the 09:00 copy says 100 and the other 2 say 140 and 110.

The table shows the bind. DISTINCT collapses the key if you select only the key and keeps the other columns if you select them. It can't do both, because a difference in any selected column keeps the row. That is the rule to state when the interviewer asks why the first attempt failed: 'DISTINCT compares the whole selected row, and these rows differ in columns outside the key, so the keyword sees 5 different rows.' The fix is the ranked CTE, which collapses on the key and carries the other columns of the chosen row along with it.

A second family of failures hides inside values that look equal. Postgres compares strings case-sensitively and without padding, and so do Snowflake and BigQuery. On those engines 'Alice@Example.com ' with a trailing space and 'alice@example.com' are different strings, and on every engine a NULL address and an empty one differ. DISTINCT on a customers table with these rows returns every one of them there, and a reader looking at the sample sees 2 copies of Alice and cannot work out why the count did not drop. SQL Server and older MySQL collations ignore case and trailing spaces and may fold the emails, so an explicit LOWER(TRIM(...)) is what makes the key behave the same everywhere. The duplicate is real; the equality test is too strict.

LOWER(TRIM(email)) maps both of Alice's spellings and both of Bob's to one value each, and COALESCE(address, '') replaces the NULL with a blank that can be compared. Bob's 2 rows now agree in every column, and DISTINCT over this result folds them. Alice's do not: one still says 12 Oak St and the other says blank, so DISTINCT keeps both even though the normalised email proves they describe one person. Normalisation builds a key out of messy columns, and the key-based pattern from the previous section is what removes the copies.

Alice's 2 rows share the partition, the ORDER BY puts the row with an address first, and the result has 2 rows, one per person, with Alice's carrying 12 Oak St. It is the same shape as before with the key built from expressions instead of stored columns. Say that as you write it: 'the key is an expression, so I partition on the expression and keep the most complete copy.'