Skip to content

Noch nicht auf Deutsch verfügbar. Alles auf Deutsch ansehen

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.

  1. LAG(close) on Tuesday's row returns Monday's ₦50
  2. 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

Facts last checked on 4 November 2026.

Education, not advice: the Stockertim Dictionary explains what terms mean. It never recommends buying or selling anything.

English