3 / 6 SQL Window Functions & Advanced

Top Three Per Customer

SELECT customer_id, order_id, total
FROM orders
WHERE row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) <= 3;

What happens when this runs?

A

It fails because PARTITION BY may only be used when the query also has a GROUP BY on the same column.

B

It fails. Window functions are permitted only in the SELECT list and ORDER BY, so the call has to move into a subquery and the filter applied to its output column.

C

It returns the three largest orders per customer. The planner rewrites the predicate into a subquery automatically.

D

It returns three rows in total, because the window is evaluated once for the whole result.