SQL · medium

Deduplicate rows keeping the most recent record per user.

Asked in Analytics Engineer interviews, in the SQL round.

Short answer

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) = 1.

Sample data

These tables are loaded into an in-browser engine on this page, so you can run your own answer against them. No account needed.

user_records

How to answer it

Definition

One row per user, and specifically the newest one. This is a ranking problem, not a DISTINCT problem: DISTINCT collapses identical rows, but these rows differ and you want a particular one.

Example

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY user_id
           ORDER BY updated_at DESC, id DESC
         ) AS rn
  FROM   user_records
)
SELECT * FROM ranked WHERE rn = 1;

Output

user_id | email           | updated_at
--------+-----------------+---------------------
    101 | ada@example.com | 2025-03-04 11:20:00
    204 | ben@example.com | 2025-02-27 09:05:00

Why ROW_NUMBER and not RANK

ROW_NUMBER always yields exactly one winner per partition. RANK returns every tied row, so two records written in the same second both come back and the duplicate survives. That is why the tiebreaker matters: ORDER BY updated_at DESC, id DESC makes the choice deterministic rather than whatever the engine happened to scan first.

Key points

• PARTITION BY the key that defines a duplicate. Getting that key wrong is the actual bug most of the time.
• Add a tiebreaker to the ORDER BY, or reruns can pick different winners and the output is not reproducible.
• ROW_NUMBER for exactly one, RANK if you genuinely want all tied winners.
• QUALIFY rn = 1 does this in one statement on Snowflake, BigQuery and DuckDB.
• SELECT DISTINCT solves a different problem and cannot pick the newest.

FAQ

• What about DISTINCT ON? Postgres has it and it is neater: SELECT DISTINCT ON (user_id) * ... ORDER BY user_id, updated_at DESC.
• How do I delete rather than select? Same ranking in a CTE, then DELETE ... WHERE id IN (SELECT id FROM ranked WHERE rn > 1).
• Is GROUP BY user_id with MAX(updated_at) enough? Only to find the timestamp. Retrieving the rest of that row still needs a join back or a window.

The 30-second answer

ROW_NUMBER partitioned by the user and ordered by the timestamp descending, then keep rank one. On an engine with QUALIFY that is a single statement, otherwise a CTE with the filter outside.

Two things I would be explicit about. ROW_NUMBER rather than RANK, because RANK returns every tied row and the duplicate survives. And a tiebreaker in the ORDER BY, usually the primary key, so two records written in the same second resolve the same way on every run. Without it the query is not reproducible, which tends to surface later as a dashboard that changes when the data has not.

In the interview

• What they are checking: ROW_NUMBER versus RANK on ties, and whether you make the result deterministic.
• Common mistake: GROUP BY user_id with MAX(updated_at), which gives the latest timestamp but mixes columns from different rows.

Practice this for real

• Check your resume against a Analytics Engineer job description, free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews, two free, no card