Window functions
Rank, aggregate, and compare rows within partitions while preserving row-level detail.
Window essentials
ROW_NUMBER
Sequential numbering within a partition.
RANK
Keep ties together and leave gaps.
SUM over window
Aggregate values across a partition while keeping row-level output.
LAG / LEAD
Read a value from a previous or following row without a self-join.
NTILE
Split ordered rows into a fixed number of buckets.
Core idea
A window function computes a value over a set of rows related to the current row, without collapsing the result set like a normal aggregate.
Example
SELECT
month,
revenue,
SUM(revenue) OVER (
ORDER BY month
) AS running_total
FROM monthly_revenue;Common gotchas
PARTITION BYchanges the group over which the calculation is evaluatedORDER BYinside the window controls the sequence of the calculation- when ranking, ties and ordering matter a lot for reproducibility