A table has duplicate rows for the same business key. Write SQL to keep only the latest version of each.

TL;DR: Partition by the business key with ROW_NUMBER ordered by recency, keep rn = 1, and end the ORDER BY with a unique column so reruns keep the same survivor. In a warehouse, prefer materializing the deduped result over an in-place DELETE and keep the raw layer append-only.

How to approach it

Ask what defines "duplicate" (full-row identical vs same business key with different payloads) and what defines "latest" (an updated_at, a load timestamp, a version number). The pattern is the same; the keys differ. Also ask whether they want a SELECT producing clean output (the common warehouse case) or a destructive DELETE.

A strong answer

The canonical idiom is ROW_NUMBER() partitioned by the business key, ordered by recency, keeping row 1:

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC, _loaded_at DESC, id DESC
    ) AS rn
  FROM customers_raw
)
SELECT * EXCLUDE (rn)   -- Snowflake; otherwise list columns
FROM ranked
WHERE rn = 1;

The detail that scores points: the ORDER BY includes deterministic tiebreakers. If two rows share the same updated_at and you order by it alone, the engine picks an arbitrary winner, and may pick a different winner on rerun. That makes your pipeline non-idempotent: downstream diffs churn, and backfills don't reproduce history. Appending the primary key (or load timestamp) makes dedupe stable.

For a destructive in-place delete in a transactional database:

DELETE FROM customers
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (
             PARTITION BY customer_id
             ORDER BY updated_at DESC, id DESC
           ) AS rn
    FROM customers
  ) t
  WHERE rn > 1
);

In warehouse practice, say you'd rarely DELETE: you'd materialize a deduped model (dbt-style) over the raw table, keeping the raw layer append-only as the audit trail. That one sentence signals real pipeline experience.

If duplicates are fully identical rows, SELECT DISTINCT suffices, but say why it doesn't work for the keyed case (it can't choose between conflicting payloads).

The Frankenstein-row bug from the table below deserves two rows of proof, because it produces data that was never true rather than merely stale (executed in sqlite). Customer 42 has an old row (login olivia_k, plan pro, updated January) and a new row (login newname_24, plan free, updated February). The GROUP BY customer_id with MAX() on every column returns:

42 | olivia_k | pro | 2024-02-05

The old login (alphabetically later), the old plan (pro > free as strings), stamped with the new timestamp. That row never existed: the customer downgraded and renamed their account, and the "deduplicated" output says they are a pro-plan customer as of February. ROW_NUMBER returns the actual February row. The mechanism is that MAX per column picks each column's winner independently, so the output mixes versions, and the February timestamp makes it look authoritative. This is the worst kind of pipeline bug: plausible, recent-looking, and assembled from parts of records that were each individually true.

Two questions decide the query: is updated_at unique per key (if not, add a tiebreak or you get non-deterministic output), and are you cleaning history once or preventing this daily. The second one is usually the real ask.

What interviewers probe next

• "Why ROW_NUMBER and not RANK?", RANK keeps all tied rows; you want exactly one survivor.
• "How did the duplicates get there?", at-least-once delivery, retried loads, no MERGE key; leads naturally to idempotent pipeline design.
• "Dedupe 5B rows in Spark?", same logic via Window.partitionBy(...).orderBy(...), or dropDuplicates only if you don't care which row wins; mention the shuffle cost and key skew.
• "How do you prevent rather than cure?", MERGE on the business key at load time, or unique constraints/expectations with quarantine.

Common mistakes

• SELECT DISTINCT as the whole answer when payloads differ.
• Non-deterministic ORDER BY (no tiebreaker), the subtle one interviewers fish for.
• GROUP BY key with MAX() on every column, Frankenstein rows that mix fields from different versions; explain why that's wrong if you mention it.
• Deleting from the raw layer instead of deduping downstream.