Window functions

Sources: SQL analytics practice · DDIA Ch. 6 (partitioning & shuffles)

Anatomy

FUNCTION() OVER (
    PARTITION BY col_a      -- slice into groups
    ORDER BY col_b          -- sort within group
    ROWS/RANGE BETWEEN ...  -- frame (optional)
)

Ranking functions

• ROW_NUMBER() — unique sequence (1,2,3,4) even on ties
• RANK() — ties share rank, gaps after (1,2,2,4)
• DENSE_RANK() — ties share rank, no gaps (1,2,2,3)

Classic pattern: top N per group

WITH ranked AS (
    SELECT *, RANK() OVER (
        PARTITION BY user_id ORDER BY revenue DESC
    ) AS rk
    FROM sessions
)
SELECT * FROM ranked WHERE rk <= 3;

Analytics

• LAG/LEAD — prior/next row (MoM growth)
• SUM/AVG() OVER(...) — running totals without collapsing rows

MPP performance

Window functions trigger a shuffle — all rows with the same PARTITION BY key land on one node. Low-cardinality partitions cause data skew.