Problem Statement
Explain how you would use a CTE to deduplicate users by email while keeping the most recent row.
Explanation
First, rank rows per email by a stable order such as created time or an auto id. Use ROW_NUMBER in a CTE to assign one to the “keeper”. This is easy to reason about and test.
Then filter to the rows with row number equal to one. You get exactly one row per email without a self join. This pattern is clear and works across engines with window support.
Code Solution
SolutionRead Only
WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) rn FROM users ) SELECT * FROM ranked WHERE rn=1;
Practice Sets
This question appears in the following practice sets:
