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.

How to answer it

Number the rows within each user, newest first, and keep number one.

SELECT user_id, email, updated_at
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM users_raw
) t
WHERE rn = 1;

ROW_NUMBER, not RANK: if two rows share the same updated_at, RANK gives both a 1 and you keep both. Add a tiebreaker to the ORDER BY (an ingestion timestamp, an id) so the choice is deterministic rather than whatever the engine happened to scan first. Postgres also has DISTINCT ON (user_id) ... ORDER BY user_id, updated_at DESC, which is shorter but not portable.

If the question is about a pipeline rather than a query, the same window becomes the dedup step in an incremental model, and the tiebreaker is what makes two runs produce the same output.

• 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.

Related questions

• Write a query to find the second highest salary from an Employees table.
• Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.
• Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
• Calculate the median order value using SQL.
• Find the top 3 products by revenue within each category.
• Compute month-over-month user retention from an events table.

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