What is a window function in SQL?
A window function performs a calculation across a set of rows related to the current row, without collapsing them into one, unlike a normal aggregate. It powers running totals, moving averages, rankings and period-over-period comparisons while still returning every underlying row.
Where a plain aggregate reduces many rows to one, a window function computes over a 'window' of rows but keeps each row in the output. It is the tool behind many analytical questions.
What window functions enable:
- Running totals cumulative revenue over time.
- Moving averages smoothing a noisy metric.
- Rankings top-N products per category using RANK or ROW_NUMBER.
- Period-over-period comparing each month to the previous with LAG or LEAD.
They use an OVER clause with optional PARTITION BY (reset per group) and ORDER BY (sequence within the window). Powerful, but among the harder SQL to write correctly.
Many real analytical questions, 'show month-over-month growth', 'rank customers by spend within each region', 'a 7-day moving average', require window functions under the hood. iDBQuery generates them for you from plain language, so you can ask for a running total or a ranked list without knowing the syntax. It runs the query against your live data and cites the result back to source rows, and because it can operate across joined sources, those window calculations can span data that originally lived in different systems.
Updated 2026-06-22