Duplicate wishlist rows: keep the first, list the rest

Medium

Question

In the wishlist table, a user should save a product only once: the pair user_id and product_id should be unique. But the rule was never enforced, so some pairs are saved more than once.

Before anyone deletes anything, make a report of the rows that would be removed. For every pair, keep the row with the oldest added_at. If two rows have exactly the same added_at, keep the one with the smaller id. Every other row of the pair is a row to remove.

Return id (the row to remove), user_id, product_id and kept_id (the id of the row that is kept for the same pair). Pairs saved only once are not in the result. Sort by id from low to high.

The order of the rows matters for this question.

deduplication
window function
row number
first value
partition by
unique constraint
cte

Read first: Lesson 48, SQL patterns that interviews repeat

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

wishlist4 columns · 9 rowsnot run yet

CREATE TABLE wishlist (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, product_id INTEGER NOT NULL, added_at TEXT NOT NULL);

Example rows of the wishlist table
id	user_id	product_id	added_at
1	1	10	2026-01-03
2	1	11	2026-01-04
3	1	10	2026-01-01
4	2	10	2026-01-02
5	2	12	2026-01-05
6	1	10	2026-01-03
7	2	12	2026-01-09
8	3	11	2026-02-01
9	3	11	2026-02-01

About this SQL

PARTITION BY can take more than one column, and a pair of columns is one key. FIRST_VALUE(id) OVER (...) gives the id of the first row of the same group in the window order, so every row of a group can show which row is the keeper. A named window (WINDOW w AS (...)) lets two window functions share one definition; it needs SQLite 3.28. The query only reads: a DELETE is not allowed in this round, and it is safer to look at the list first.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready