Other Common Window Functions

You have now seen the main shape of window functions: a function call followed by an `OVER (...)` clause.

The same window definition can be paired with several families of functions:

**Aggregate window functions** reuse aggregates you already know, but without collapsing rows:

`SUM(...) OVER (...)`, `AVG(...) OVER (...)`, `MIN(...) OVER (...)`, `MAX(...) OVER (...)`, and `COUNT(...) OVER (...)`

Use these for totals, running totals, moving averages, group averages beside detail rows, and percent-of-total calculations.

**Ranking functions** assign positions inside an ordered window:

`ROW_NUMBER()` gives every row a unique sequence number. `RANK()` gives tied rows the same rank and leaves gaps. `DENSE_RANK()` gives tied rows the same rank without gaps. `NTILE(n)` splits ordered rows into roughly equal buckets, such as quartiles with `NTILE(4)`.

Use these for top-N queries, leaderboards, deduplication, and percentile-style grouping.

**Offset functions** compare a row to nearby rows:

`LAG(...)` looks backward. `LEAD(...)` looks forward.

Use these for change-over-time analysis, previous/next status, gaps between events, and month-over-month comparisons.

**Value functions** pick values from a window frame:

`FIRST_VALUE(...)` returns the first value in the current frame. `LAST_VALUE(...)` returns the last value in the current frame. `NTH_VALUE(..., n)` returns the nth value in the current frame.

These are powerful, but they depend heavily on the window frame. If `LAST_VALUE` surprises you, check whether your frame ends at the current row or at the end of the partition.

A useful rule of thumb: choose the function for the question, then choose `PARTITION BY` for the groups, `ORDER BY` for the sequence, and a frame when the exact row range matters.