1 · Reporting patterns and edge cases · 25 MIN

Select one representative from each duplicate group

Select one representative from each duplicate group

ROW_NUMBER labels rows within a partition while keeping their identities. To retain the earliest event for each category and amount combination, partition by both fields and order by the unique ID. Filter the row number in an outer query because window results are not available in the same query’s WHERE phase. This creates a deduplicated result; it does not delete data or prove that equal values represent the same real-world event.

Define the output row grain and ordering before choosing SQL operators.

Read the example

WITH numbered AS (SELECT id, category, amount, ROW_NUMBER() OVER (PARTITION BY category, amount ORDER BY id) AS rn FROM events) SELECT id, category, amount FROM numbered WHERE rn = 1 ORDER BY id;

[[1,"food",5],[2,"travel",3],[3,"food",7],[4,"travel",null]]

Your challenge

Return id, category and amount for the earliest row in each category-and-amount group, ordered by id.

Solution cost: Aggregation scans input; window ordering can add O(n log n) sorting. Inspect the database plan. time · Grouping or window buffers depend on the plan and partition size. space

Common trap

Equal values are not necessarily duplicate business events; define the deduplication key first.

Further reading: SQLite window functions