Window functions

A window function computes over a set of rows related to the current row, but keeps every row in the output. The OVER clause defines that window.

PARTITION BY restarts the calculation per group; ORDER BY inside OVER makes the window running, which is how you build cumulative totals.

ROW_NUMBER, RANK and DENSE_RANK number rows inside each partition — wrap them in a CTE to pick the top N per group.