SQL Window Functions: Ranking, Running Totals, LAG, LEAD and Moving Averages

SQL window functions are one of the most powerful tools for analytical queries because they calculate values across related rows without collapsing the original result set. Unlike GROUP BY, which reduces many rows into a smaller number of groups, a window function returns one value for every input row while adding an analytical column. The OVER clause defines the window through three optional components: PARTITION BY divides rows into independent groups, ORDER BY establishes their sequence, and a frame determines exactly which rows are included in the calculation. This makes window functions suitable for customer-level totals, percentage-of-total calculations, rankings, and running measures while preserving row-level detail.
Ranking functions provide several ways to assign positions while handling ties differently. ROW_NUMBER() assigns a unique sequential number and is useful for deduplication or selecting one record per group. RANK() gives tied rows the same position and leaves gaps after ties, making it appropriate for competition-style standings. DENSE_RANK() also assigns equal ranks to ties but does not leave gaps, which makes it useful for top-N tiers. NTILE() distributes rows into approximately equal buckets for quartiles, deciles, or cohorts. A critical detail is determinism: ROW_NUMBER() over a non-unique ordering can produce different assignments between executions, so a unique tiebreaker such as a primary key should be added. For Top-N-per-group queries, ranking is calculated inside a CTE or subquery and filtered in the outer query because window functions are evaluated after WHERE.
Offset functions allow SQL to compare the current row with neighbouring rows without constructing self-joins. LAG() retrieves a value from an earlier row, while LEAD() looks ahead, making them ideal for month-over-month changes, period comparisons, and time-to-next-event calculations. FIRST_VALUE() and LAST_VALUE() retrieve values from the edges of a window, but LAST_VALUE() requires particular care because the default frame can end at the current row, causing it to return the current value rather than the final value of the partition. Aggregate functions such as SUM, AVG, COUNT, MIN, and MAX can also operate as windows. With ORDER BY, they can produce cumulative calculations such as running totals; without ordering, they can repeat a partition-level summary on every row. An explicit ROWS frame can create fixed-width calculations such as seven-row moving averages.
Window frames are particularly important when duplicate ordering values or time gaps exist. ROWS defines a physical number of rows, while RANGE groups rows according to the ordering value, meaning duplicate order values can be treated as peers. Choosing the wrong frame can therefore produce unexpected running totals or moving-window results. The presentation also applies window functions to practical analytical patterns including Top-N per group, deduplication, gaps and islands, sessionisation, and cohort retention. In the cohort example, MIN(...) OVER (PARTITION BY cust_id) identifies each customer’s first month without collapsing the order rows, month arithmetic determines the customer’s offset from that cohort, and a windowed MAX supplies the month-zero population used as the retention denominator.
Performance depends largely on the sorting required by each distinct window specification. Each different PARTITION BY and ORDER BY combination may require its own sort, after which the window calculations can generally be completed in a pass over the sorted data. Reusing a named window specification can allow multiple functions to share the same sort, while an index matching the partition and ordering columns may eliminate or reduce sorting work. Execution plans should therefore be inspected for WindowAgg and Sort operations when tuning analytical queries. The presentation also highlights several common mistakes: filtering a window result directly in WHERE, using LAST_VALUE() without extending its frame, relying on non-deterministic ROW_NUMBER(), confusing RANGE with ROWS, and assuming window functions are always preferable to a simple GROUP BY. The right approach is to define the window precisely, choose ranking and frame semantics intentionally, and verify performance with the execution plan.