SQL window functions, explained simply
GROUP BY squashes rows into totals. A window function calculates across rows but keeps every row, so each row can show its own value and something about its neighbours: its rank, the running total so far, last month's number.
In Excel you'd drag formulas like =SUM($B$2:B2) or =B3-B2 down a column. Windows do that in one line. The OVER (…) part defines the "window": PARTITION BY restarts it for each group, ORDER BY sets the order to walk through.
Syntax
FUNCTION() OVER (
PARTITION BY group_col -- optional: restart per group
ORDER BY sort_col -- optional: order within it
)Try itEdit the query, then Run
| month | units | running_total | change_vs_last_month | best_month_rank |
|---|---|---|---|---|
| 2026-01 | 313 | 313 | empty | 2 |
| 2026-02 | 289 | 602 | -24 | 4 |
| 2026-03 | 289 | 891 | 0 | 4 |
| 2026-04 | 308 | 1199 | 19 | 3 |
| 2026-05 | 366 | 1565 | 58 | 1 |
| 2026-06 | 278 | 1843 | -88 | 6 |
6 rows
The ones you'll use most
SUM(x) OVER (ORDER BY d): running totalAVG(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW): rolling averageLAG(x)/LEAD(x): the previous / next row's value, for period-over-period changeROW_NUMBER(),RANK(),DENSE_RANK(): rankings, and top N per groupSUM(x) OVER (PARTITION BY g): the group total on every row, for "% of total"
Window functions work in Postgres, MySQL 8+, SQL Server, BigQuery, Snowflake and SQLite.
Your turn
Each product's share of total units sold: product_id, units, and pct_of_total (units ÷ all units × 100, rounded to 1 decimal).
SUM(SUM(quantity)) OVER () is the grand total on every row.
This page on one card
