Engineering practice · 25 MIN

Keep every row tied for first place

Ranking semantics are a business decision.

ROW_NUMBER assigns a distinct position, RANK leaves gaps after ties, and DENSE_RANK does not. To keep all highest-value sales per customer, rank each partition by amount descending and filter in a CTE. Exclude null amounts before ranking so an all-null customer has no winner. Window ordering determines rank; outer ORDER BY determines displayed row order.

Treat the function as a small service: define a contract, maintain an invariant, and test the boundaries.

Read the example

SELECT id, DENSE_RANK() OVER (PARTITION BY customer ORDER BY amount DESC) AS position FROM sales WHERE amount IS NOT NULL ORDER BY id;

[[1,1],[2,1],[3,2],[4,1]]

Your challenge

Return id, customer, amount for every sale tied for the largest non-null amount within its customer. Order by customer then id.

Solution cost: Typically O(n log n) for partition sorting; inspect the actual plan. time · Up to O(n) sort/window workspace; engine dependent. space

Common trap

ROW_NUMBER throws away tied winners.

Further reading: SQLite window functions