Stockertim Dictionary · Technology
Window function
Also called analytic function, window functions
In short
An SQL calculation that looks across a set of related rows, such as earlier days for the same ticker, while keeping every row in the result.
- Advanced
- Technology
- Checked
In detail
Ordinary SQL aggregation, such as GROUP BY with SUM or AVG, collapses many rows into one. A window function does a similar calculation but hands back a value on every row, based on a window of neighbouring rows. The window is set with an OVER clause: PARTITION BY splits the data into groups, for instance one per ticker, and ORDER BY fixes the sequence inside each group, for instance by trading date.
Widely used examples include LAG and LEAD, which fetch the value from an earlier or later row; ROW_NUMBER and RANK, which number rows within each group; and running SUM or AVG over a frame such as the previous 20 rows, which produces a moving average. With LAG, a daily return is today's close over the previous close for the same ticker, minus one, worked out in a single query with no danger of mixing one company's prices into another's series.
Ordering is where errors creep in. If a date is absent because of a holiday or a trading suspension, LAG takes whatever row came before, which may be several calendar days earlier. Frames counted in rows and frames counted in time behave differently when data has gaps, so analysts confirm which kind the query uses.
Worked example
Kestrel Foods closes at ₦50 on Monday and ₦52 on Tuesday.
- LAG(close) on Tuesday's row returns Monday's ₦50
- Daily return: 52 / 50 − 1 = 0.04
Tuesday's row shows a 4% return, while Monday's row, with no earlier price in the table, shows a blank.
In practice
Risk and research teams use window functions to compute returns, rolling volatility, rankings within sectors and the first or last trade of each session inside the database, which avoids exporting millions of rows into a spreadsheet.
Watch out
- Leaving out PARTITION BY lets LAG reach into the previous ticker's last row.
- Window functions run after WHERE filters, so filtering first can remove the rows a calculation needs.
Learn it properly
Official sources
- API technical and data standards · Government Digital Service
- Engineering Statistics Handbook: moving average · National Institute of Standards and Technology
Facts last checked on 4 November 2026.
Education, not advice: the Stockertim Dictionary explains what terms mean. It never recommends buying or selling anything.