Beyond ranking, window functions can look across rows to compute running totals, moving averages, and comparisons between a row and its neighbours. These are the analytical staples behind trend lines and period-over-period reports: cumulative runs through an innings, a player's score compared to their previous match, or a moving average that smooths noisy data, all while keeping every detail row intact.
Two families do this work. The offset functions LEAD and LAG reach forward or backward to a neighbouring row, letting each row see the next or previous value. The aggregate window functions, SUM, AVG, COUNT and friends used with OVER, compute running and windowed aggregates over a sliding set of rows defined by a frame. Together they express almost any cross-row calculation analysts need.
These functions are where SQL becomes a genuine analytics language, doing work people often think requires a spreadsheet or Python. Running totals, growth rates, and rolling averages fall out of a single query, computed efficiently inside the database. Understanding offsets and frames is the final piece that lets you express sophisticated time-series and comparative analysis directly in SQL.