Window and series functions

Look at a series of rows: previous and next values, running totals, moving windows, time windows and ranks, with OVER (...).

• Category: Functions — window and series
• Keywords: window function, series function, analytic function, OVER, PARTITION BY, ROWS, PRECEDING, FOLLOWING, UNBOUNDED, UNBOUNDED FOLLOWING, CURRENT ROW, RANGE, RANGE BETWEEN, INTERVAL, frame, centered moving average, future rows, time window, last 7 days, running total, running product, cumulative product, running max, high so far, running min, percentile rank, cumulative distribution, quartile, decile, bucket, first value, last value, cumulative sum, moving sum, moving max, rolling, previous row, next row, previous close, change from yesterday, top N per group, ranking, running sum, cumulative, prev, shift, diff, next, percent change, rownum, row number, QUALIFY, window with GROUP BY
• See also: Technical indicators, Aggregate functions, Statistics functions, SAMPLE BY, UNION and subqueries, WHERE

A window function (or series function) gives a value for every row, and it may look at other rows to make it: the row before, the rows of the same symbol, the last 5 rows. Unlike GROUP BY, the rows stay; nothing is merged.

• Series functions always work on a series: lag, lead, cumsum, cumprod, cummax, cummin, pct_change, row_number, rank, dense_rank, percent_rank, cume_dist, ntile, first_value, last_value, nth_value, and the indicators sma, ema, rsi, atr (technical indicators).
• More series functions have their own pages: returns, drawdown and z-score (returns and risk), and the indicator families momentum (MACD, stochastic), volatility (Bollinger bands), trend (ADX, supertrend) and volume (OBV, MFI).
• Aggregates (sum, avg, min, max, count, ...) become window functions when you write OVER (...) after them.

The order of the series. Without OVER, or with OVER but without ORDER BY, the series is all result rows in time order (the time column of the table). A table or subquery without a time column keeps the order the rows come in. Rows with the same time keep their storage order.

Rules

1. WHERE runs first: the series only has the rows that WHERE keeps. LIMIT and ORDER BY run after: they do not change the values.
2. The rows of the result are not sorted in series order by themselves. Add ORDER BY time (or ORDER BY symbol, time) to read them.
3. Window functions can only be in the select list. To filter on one (WHERE rn = 1), put the query in a subquery.
4. With GROUP BY or SAMPLE BY in the same SELECT, the window runs over the groups, after they are made: sum(sum(qty)) OVER (ORDER BY time) is a running total of the grouped sums. Inside OVER, use the group keys or aggregates, not the raw columns.
5. One window function cannot be inside another (lag(sma(close, 3))), and an aggregate cannot take a window function (sum(lag(close) OVER (...))). The error says so (PostgreSQL 42803). Use one subquery level per step: SELECT sum(x) FROM (SELECT lag(close) OVER (...) AS x FROM t) s.

Example: Yesterday's close, change and running volume of VOLT (sample data: sample)

SELECT time, close,
       lag(close) AS prev_close,
       round(close - lag(close), 2) AS change,
       round(pct_change(close), 2) AS change_pct,
       cumsum(volume) AS running_volume
FROM ochl_day
WHERE symbol = 'VOLT' AND time >= '2026-09-06'
ORDER BY time;

Result:

(5 rows)

Example: A sum of yesterday's closes: the window in a subquery (sample data: sample)

SELECT round(sum(prev_close), 2) AS sum_prev_close
FROM (SELECT lag(close) OVER (ORDER BY time) AS prev_close FROM ochl_day WHERE symbol = 'VOLT') s;

Result:

(1 row)

Example: The same without the subquery is refused (sample data: sample; shows an error)

SELECT sum(lag(close) OVER (ORDER BY time)) FROM ochl_day WHERE symbol = 'VOLT';

Result:

Error: line 1, column 12: 'lag' is a window function and cannot be used inside the aggregate 'sum'; compute it in a subquery first: SELECT sum(x) FROM (SELECT lag(...) OVER (...) AS x FROM t) s

Example: Top trade of each symbol: the window inside, the filter outside (sample data: sample)

SELECT symbol, transaction_id, buyer, amount
FROM (SELECT symbol, transaction_id, buyer, amount,
             row_number() OVER (PARTITION BY symbol ORDER BY amount DESC) AS rn
      FROM trade_log) t
WHERE rn = 1
ORDER BY amount DESC;

Result:

(4 rows)

OVER (...)

Syntax:

function(arguments) OVER ()
function(arguments) OVER ([PARTITION BY expr, ...]
                          [ORDER BY expr [ASC | DESC], ...]
                          [ROWS n PRECEDING | ROWS UNBOUNDED PRECEDING
                           | ROWS BETWEEN start AND end
                           | RANGE BETWEEN start AND end])

start, end:  UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING

OVER says which rows the function sees for each row: the window. All three parts are optional.

• Works with: the series functions and every aggregate. Other functions give an error: 'upper' cannot be used with OVER.
• Notes: the frame is the part of the window that an aggregate reads: ROWS ... or RANGE .... A series function (lag, sma, rank, ...) uses PARTITION BY and ORDER BY, and ignores the frame; only first_value, last_value, nth_value, and zscore and minmax_scale without a period read it. There are no named windows (WINDOW w AS) and no FILTER. DISTINCT inside a window aggregate is ignored (count(DISTINCT x) OVER () counts all values).

Example: The same sum over three different windows (sample data: sample)

SELECT time, volume,
       sum(volume) OVER () AS all_rows,
       sum(volume) OVER (ORDER BY time) AS running,
       sum(volume) OVER (ORDER BY time ROWS 1 PRECEDING) AS last_2_days
FROM ochl_day
WHERE symbol = 'CITY' AND time >= '2026-09-06'
ORDER BY time;

Result:

(5 rows)

Example: Only series functions and aggregates can have OVER (sample data: sample; shows an error)

SELECT upper(symbol) OVER () FROM ochl_day;

Result:

Error: line 1, column 8: 'upper' cannot be used with OVER

PARTITION BY (window)

Syntax:

function(arguments) OVER (PARTITION BY expr [, expr ...] ...)

Splits the rows into separate series, one per value (for example one per symbol). The function starts again for every part: lag of the first CITY row is NULL, not the last COAST price.

• NULL: rows where the expression is NULL are one part together.
• Notes: without PARTITION BY, all rows are one series. With many symbols in the result, always use PARTITION BY symbol, or CITY's values mix with COAST's.
• Fast sums of windows: a GROUP BY over a subquery of row_number, rank, dense_rank, lead or lag over one table runs on the column engine when the PARTITION BY columns are the first columns the table is sorted by and include its time column (a floorsheet sorted by symbol, then time: PARTITION BY time, symbol), each call has one plain ORDER BY column, and the outer query uses sum, count, min, max or avg. A year of such a filter is then about 8 times faster. The answer is the same either way. A day with rows not saved yet is read the same way, and the step keeps at most 64 MB of window rows at a time; a NULL in the ORDER BY column runs the usual way.

Example: Previous close per symbol (sample data: sample)

SELECT time, symbol, close,
       lag(close) OVER (PARTITION BY symbol ORDER BY time) AS prev_close
FROM ochl_day
WHERE time >= '2026-09-09'
ORDER BY symbol, time;

Result:

(6 rows)

Example: Wrong: without PARTITION BY the symbols are one series (sample data: sample)

SELECT time, symbol, close, lag(close) AS prev_close_mixed
FROM ochl_day
WHERE time >= '2026-09-09'
ORDER BY time, symbol;

Result:

(6 rows)

Example: Each symbol's share of the day's volume (sample data: sample)

SELECT time, symbol, volume,
       round(volume * 100.0 / sum(volume) OVER (PARTITION BY time), 1) AS share_pct
FROM ochl_day
WHERE time >= '2026-09-09'
ORDER BY time, share_pct DESC;

Result:

(6 rows)

ORDER BY (window)

Syntax:

function(arguments) OVER ([PARTITION BY ...] ORDER BY expr [ASC | DESC] [, expr [ASC | DESC] ...] ...)

The order of the rows inside the window. It decides what "previous" means for lag, what a running total adds up, and who is first for rank.

• Default: without ORDER BY, time order (see the top of this page).
• NULL: rows where the order value is NULL come last, also with DESC. NULLS FIRST is accepted here but has no effect yet.
• Notes:
  • This ORDER BY only orders the window. The result rows need their own ORDER BY at the end of the query.
  • An aggregate with ORDER BY and no ROWS is a running value: from the first row to this row. When two rows have the same order value, KDB still adds them one at a time (PostgreSQL would give both rows the same total; in KDB write RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for that, see RANGE).
  • With DESC, lead looks at the row that comes next in the window, which is the earlier time.

Example: Biggest volume first: rank the days of COAST (sample data: sample)

SELECT time, volume, row_number() OVER (ORDER BY volume DESC) AS volume_rank
FROM ochl_day
WHERE symbol = 'COAST' AND time >= '2026-09-06'
ORDER BY time;

Result:

(5 rows)

Example: Running total of the day's turnover, trade by trade (sample data: sample)

SELECT transaction_id, symbol, amount,
       sum(amount) OVER (ORDER BY transaction_id) AS running_turnover
FROM trade_log
WHERE date IN '2026-09-09'
ORDER BY transaction_id;

Result:

(8 rows)

ROWS n PRECEDING

Syntax:

aggregate(x) OVER ([PARTITION BY ...] [ORDER BY ...] ROWS n PRECEDING)

A moving window: this row and the n rows before it (so n + 1 rows). ROWS 4 PRECEDING is a 5-day window. At the start of a series there are fewer rows before, and the window is smaller.

• Notes: the short form of ROWS BETWEEN n PRECEDING AND CURRENT ROW. It has an effect on aggregates only. The first rows use a smaller window, they are not NULL (unlike sma, which is NULL until it has n rows: see sma).

Example: 5-day high, 5-day low and 3-day average volume (sample data: sample)

SELECT time, symbol, close,
       max(high) OVER (PARTITION BY symbol ORDER BY time ROWS 4 PRECEDING) AS high_5d,
       min(low) OVER (PARTITION BY symbol ORDER BY time ROWS 4 PRECEDING) AS low_5d,
       round(avg(volume) OVER (PARTITION BY symbol ORDER BY time ROWS 2 PRECEDING)) AS avg_vol_3d
FROM ochl_day
WHERE symbol = 'CITY' AND time >= '2026-09-01'
ORDER BY time;

Result:

(8 rows)

Example: The short form only goes back: for rows after, write BETWEEN (sample data: sample; shows an error)

SELECT sum(volume) OVER (ORDER BY time ROWS 2 FOLLOWING) FROM ochl_day;

Result:

Error: line 1, column 40: a frame that starts after the current row needs an end: write BETWEEN n FOLLOWING AND ...

ROWS UNBOUNDED PRECEDING

Syntax:

aggregate(x) OVER ([PARTITION BY ...] [ORDER BY ...] ROWS UNBOUNDED PRECEDING)

A running window: from the first row of the part to this row. It is the same as ORDER BY without ROWS, written out.

• Notes: a running max(high) is the highest price so far ("all-time high up to this day").

Example: Highest high so far, and the running number of trading days (sample data: sample)

SELECT time, high,
       max(high) OVER (ORDER BY time ROWS UNBOUNDED PRECEDING) AS high_so_far,
       count(*) OVER (ORDER BY time ROWS UNBOUNDED PRECEDING) AS day_no
FROM ochl_day
WHERE symbol = 'VOLT' AND time >= '2026-09-01'
ORDER BY time;

Result:

(8 rows)

ROWS BETWEEN n PRECEDING AND CURRENT ROW

Syntax:

aggregate(x) OVER ([PARTITION BY ...] [ORDER BY ...] ROWS BETWEEN n PRECEDING AND CURRENT ROW)
aggregate(x) OVER ([PARTITION BY ...] [ORDER BY ...] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

The long, standard SQL way to write ROWS n PRECEDING and ROWS UNBOUNDED PRECEDING. Both forms give the same result. With BETWEEN, the end can also be after this row: see ROWS BETWEEN ... FOLLOWING.

Example: Long and short form give the same moving sum (sample data: sample)

SELECT time, volume,
       sum(volume) OVER (ORDER BY time ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS long_form,
       sum(volume) OVER (ORDER BY time ROWS 2 PRECEDING) AS short_form
FROM ochl_day
WHERE symbol = 'COAST' AND time >= '2026-09-06'
ORDER BY time;

Result:

(5 rows)

Example: The end of the frame cannot be before its start (sample data: sample; shows an error)

SELECT sum(volume) OVER (ORDER BY time ROWS BETWEEN CURRENT ROW AND 2 PRECEDING) FROM ochl_day;

Result:

Error: line 1, column 40: the frame ends before it starts; expected the end bound after the start bound

ROWS BETWEEN ... FOLLOWING

Syntax:

aggregate(x) OVER ([PARTITION BY ...] [ORDER BY ...] ROWS BETWEEN start AND end)

start:  UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING
end:    n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING

A frame that also reads rows after this row. n FOLLOWING is n rows after this row; UNBOUNDED FOLLOWING is up to the last row of the part. ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING is a centered moving window of 5 rows: this row is in the middle.

• NULL: NULL values are skipped, as in the plain aggregate. A frame with no rows (for example 1 FOLLOWING AND 3 FOLLOWING on the last row) gives NULL (count gives 0).
• Notes:
  • Near the start and the end of a part, the frame is smaller; it is not NULL.
  • The end must not be before the start. A frame cannot start with UNBOUNDED FOLLOWING, and ROWS n FOLLOWING alone is an error: write ROWS BETWEEN n FOLLOWING AND ....
  • Careful with trading signals: a value that reads future rows is fine for a chart, but a real trader did not know those prices yet.

Example: Centered 5-day average, volume still to come, and the best close of the next 3 days (sample data: sample)

SELECT time, close,
       round(avg(close) OVER (ORDER BY time ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING), 2) AS centered_avg,
       sum(volume) OVER (ORDER BY time ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS volume_from_here,
       max(close) OVER (ORDER BY time ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) AS next_3_days_max
FROM ochl_day
WHERE symbol = 'COAST' AND time >= '2026-09-01'
ORDER BY time;

Result:

(8 rows)

Example: Each day's share of the symbol's volume over all shown days (the full frame) (sample data: sample)

SELECT time, symbol, volume,
       round(volume * 100.0 / sum(volume) OVER (PARTITION BY symbol ORDER BY time
                                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 1) AS share_of_days_pct
FROM ochl_day
WHERE time >= '2026-09-08'
ORDER BY symbol, time;

Result:

(9 rows)

RANGE BETWEEN

Syntax:

aggregate(x) OVER ([PARTITION BY ...] ORDER BY value RANGE BETWEEN start AND end)
aggregate(x) OVER ([PARTITION BY ...] ORDER BY value RANGE distance PRECEDING)

distance:  number | 'interval text' | INTERVAL 'interval text'

A frame by value, not by row count. RANGE BETWEEN '7 days' PRECEDING AND CURRENT ROW reads every row whose time is at most 7 days before this row's time. This is what you want when rows are missing: a week has 5 trading days, and holidays make it fewer, so ROWS 6 PRECEDING is not always "one week".

• NULL: NULL values of x are skipped. Rows whose ORDER BY value is NULL are only peers of each other.
• Notes:
  • A distance needs exactly one ORDER BY value, and it must be a number or a time.
  • '1 month' and '1 year' are refused: they have no fixed length.
  • The distance must be 0 or more. With ORDER BY ... DESC, PRECEDING means the later times.
  • RANGE '2 days' PRECEDING (without BETWEEN) is short for RANGE BETWEEN '2 days' PRECEDING AND CURRENT ROW.

Example: 7-day window by time against 7 rows: holidays make the difference (sample data: sample)

SELECT time, close,
       count(*) OVER (ORDER BY time RANGE BETWEEN '7 days' PRECEDING AND CURRENT ROW) AS rows_in_7_days,
       round(avg(close) OVER (ORDER BY time RANGE BETWEEN '7 days' PRECEDING AND CURRENT ROW), 2) AS avg_7_days,
       count(*) OVER (ORDER BY time ROWS 6 PRECEDING) AS rows_in_7_rows
FROM ochl_day
WHERE symbol = 'CITY' AND time >= '2026-08-23'
ORDER BY time;

Result:

(15 rows)

Example: Volume of the last hour, for every minute (sample data: sample)

SELECT time, close,
       sum(volume) OVER (ORDER BY time RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW) AS volume_last_hour
FROM ochl_minutes
WHERE symbol = 'COAST' AND time IN '2026-09-10'
ORDER BY time DESC
LIMIT 3;

Result:

(3 rows)

Example: Numbers: trades of a similar size, and running totals where ties share one total (sample data: sample)

SELECT transaction_id, quantity,
       count(*) OVER (ORDER BY quantity RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING) AS similar_trades,
       sum(quantity) OVER (ORDER BY quantity RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_with_peers,
       sum(quantity) OVER (ORDER BY quantity) AS running_row_by_row
FROM trade_log
WHERE symbol = 'COAST'
ORDER BY quantity, transaction_id;

Result:

(11 rows)

Example: Months have no fixed length (sample data: sample; shows an error)

SELECT sum(volume) OVER (ORDER BY time RANGE BETWEEN '1 month' PRECEDING AND CURRENT ROW) FROM ochl_day;

Result:

Error: line 1, column 8: sum: '1 month' is not a distance like '5 days', '2 hours' or '30 minutes' (months and years are not supported)

lag

Syntax:

lag(x)
lag(x, k)
lag(x, k) OVER ([PARTITION BY ...] [ORDER BY ...])

The value of x in the row k places before this row in the series. lag(close) is the previous close.

• Returns: the type of x.
• NULL: NULL for the first k rows (there is no row before). A NULL value is taken as it is.
• Notes: k must be a whole number of 1 or more, else the error the period must be a whole number of rows.

Example: Gap up or down: today's open against yesterday's close (sample data: sample)

SELECT time, symbol, open, prev_close, round(open - prev_close, 2) AS gap
FROM (SELECT time, symbol, open, lag(close) OVER (PARTITION BY symbol ORDER BY time) AS prev_close
      FROM ochl_day
      WHERE time >= '2026-09-06') t
ORDER BY symbol, time;

Result:

(15 rows)

Example: Close of 3 days ago (sample data: sample)

SELECT time, close, lag(close, 3) AS close_3_days_ago
FROM ochl_day
WHERE symbol = 'CITY' AND time >= '2026-09-01'
ORDER BY time;

Result:

(8 rows)

Example: lag cannot be used in WHERE: use a subquery (sample data: sample; shows an error)

SELECT time, close FROM ochl_day WHERE close > lag(close);

Result:

Error: line 1, column 48: 'lag' works over a series of rows and can only be used in a SELECT list

lead

Syntax:

lead(x)
lead(x, k)
lead(x, k) OVER ([PARTITION BY ...] [ORDER BY ...])

The value of x in the row k places after this row. lead(close) is the next day's close. Useful to test a signal: "what happened the day after?"

• Returns: the type of x.
• NULL: NULL for the last k rows.

Example: Next day's close after each day (sample data: sample)

SELECT time, symbol, close,
       lead(close) OVER (PARTITION BY symbol ORDER BY time) AS next_close
FROM ochl_day
WHERE time >= '2026-09-08'
ORDER BY symbol, time;

Result:

(9 rows)

Example: Minutes between forum posts of a thread (sample data: content-forum)

SELECT id, author, created_at,
       (lead(created_at) OVER (PARTITION BY thread_id ORDER BY created_at) - created_at) / 60000 AS minutes_to_next
FROM posts
ORDER BY thread_id, created_at;

Result:

(8 rows)

cumsum

Syntax:

cumsum(x)
cumsum(x) OVER ([PARTITION BY ...] [ORDER BY ...])

The running total: the sum of x from the first row of the series to this row.

• Returns: LONG when every value is a whole number, else DOUBLE (also for DECIMAL: use sum(x) OVER (ORDER BY ...) to keep a DECIMAL exact).
• NULL: NULL values are skipped: the total stays the same. Before the first value that is not NULL, the result is NULL.
• Notes: the same as sum(x) OVER ([PARTITION BY ...] ORDER BY ...).

Example: Running volume per symbol (sample data: sample)

SELECT time, symbol, volume,
       cumsum(volume) OVER (PARTITION BY symbol ORDER BY time) AS running_volume
FROM ochl_day
WHERE time >= '2026-09-08'
ORDER BY symbol, time;

Result:

(9 rows)

Example: Running balance of the bank account (sample data: query-app)

SELECT line_id, memo, debit, credit,
       sum(debit) OVER (ORDER BY line_id) AS money_in_so_far,
       round(cumsum(coalesce(debit, 0) - coalesce(credit, 0)) OVER (ORDER BY line_id), 2) AS balance
FROM ledger
WHERE account = 1100
ORDER BY line_id;

Result:

(6 rows)

Example: New app users so far, per Nepal day (sample data: fn-dates)

SELECT day, signups, cumsum(signups) AS total_users
FROM (SELECT to_date(created_at + 20700000) AS day, count(*) AS signups
      FROM signups
      GROUP BY to_date(created_at + 20700000)) t
ORDER BY day;

Result:

(5 rows)

cumprod

Syntax:

cumprod(x)
cumprod(x) OVER ([PARTITION BY ...] [ORDER BY ...])

The running product: all values of x from the first row to this row, multiplied. Traders use it to compound returns: if a share rises 1% and then 2%, it grew by 1.01 × 1.02 = 1.0302. So cumprod(1 + return) is what 1 dollar has grown to.

• Returns: DOUBLE.
• NULL: NULL values are skipped: the product stays the same. Before the first value that is not NULL, the result is NULL. A product too big for a DOUBLE is NULL.
• Notes: a window function cannot be inside another, so compute the return in a subquery first. For the growth in percent, cum_return is shorter.

Example: Growth of 1 dollar in VOLT, day by day (sample data: sample)

SELECT time, close, ratio, round(cumprod(ratio), 4) AS growth_of_1
FROM (SELECT time, close, close / lag(close) AS ratio
      FROM ochl_day
      WHERE symbol = 'VOLT' AND time >= '2026-09-01') t
ORDER BY time;

Result:

(8 rows)

Example: Text cannot be multiplied (sample data: sample; shows an error)

SELECT cumprod(symbol) FROM ochl_day;

Result:

Error: line 1, column 8: cumprod: needs numbers, got 'CITY'

cummax

Syntax:

cummax(x)
cummax(x) OVER ([PARTITION BY ...] [ORDER BY ...])

The running maximum: the biggest value of x from the first row to this row. On prices it is the "highest so far". Works for numbers, text and times.

• Returns: the type of x.
• NULL: NULL values are skipped. Before the first value that is not NULL, the result is NULL.
• Notes: the same as max(x) OVER ([PARTITION BY ...] ORDER BY ...). Values of different kinds (a number and a text) give an error. For the fall from the highest price in percent, see drawdown.

Example: Record volume days, and how far the close is below its high so far (sample data: sample)

SELECT time, volume, cummax(volume) AS record_volume,
       CASE WHEN volume = cummax(volume) THEN 'new record' END AS note,
       round(close / cummax(close) * 100 - 100, 2) AS below_high_pct
FROM ochl_day
WHERE symbol = 'CITY' AND time >= '2026-08-23'
ORDER BY time;

Result:

(15 rows)

Example: The latest login so far, going through the users by id (sample data: query-app)

SELECT id, username, last_login, cummax(last_login) OVER (ORDER BY id) AS latest_so_far
FROM app_users
ORDER BY id;

Result:

(8 rows)

cummin

Syntax:

cummin(x)
cummin(x) OVER ([PARTITION BY ...] [ORDER BY ...])

The running minimum: the smallest value of x from the first row to this row ("lowest so far"). Works for numbers, text and times.

• Returns: the type of x.
• NULL: NULL values are skipped. Before the first value that is not NULL, the result is NULL.
• Notes: the same as min(x) OVER ([PARTITION BY ...] ORDER BY ...). Values of different kinds give an error.

Example: Lowest low so far per symbol, and the bounce from it (sample data: sample)

SELECT time, symbol, low, close, low_so_far, round((close - low_so_far) / low_so_far * 100, 2) AS above_low_pct
FROM (SELECT time, symbol, low, close,
             cummin(low) OVER (PARTITION BY symbol ORDER BY time) AS low_so_far
      FROM ochl_day
      WHERE time >= '2026-09-01') t
WHERE time >= '2026-09-08'
ORDER BY symbol, time;

Result:

(9 rows)

pct_change

Syntax:

pct_change(x)
pct_change(x) OVER ([PARTITION BY ...] [ORDER BY ...])

The change from the previous row, in percent: (x - previous) / previous × 100. On daily closes this is the daily return.

• Returns: DOUBLE (2.5 means +2.5%).
• NULL: NULL for the first row, when this or the previous value is NULL, and when the previous value is 0.

Example: Daily return of every symbol (sample data: sample)

SELECT time, symbol, close,
       round(pct_change(close) OVER (PARTITION BY symbol ORDER BY time), 2) AS return_pct
FROM ochl_day
WHERE time >= '2026-09-08'
ORDER BY symbol, time;

Result:

(9 rows)

Example: Growth of forum posts week by week (sample data: fn-dates)

SELECT week, posts, round(pct_change(posts), 1) AS growth_pct
FROM (SELECT date_trunc('week', created_at) AS week, count(*) AS posts
      FROM forum_posts
      GROUP BY date_trunc('week', created_at)) t
ORDER BY week;

Result:

(4 rows)

row_number

Syntax:

row_number()
row_number() OVER ([PARTITION BY ...] [ORDER BY ...])

Numbers the rows of each part: 1, 2, 3, ... Equal values still get different numbers.

• Returns: LONG.
• Notes: takes no argument. For rows with equal order values, which one gets the lower number follows their storage order; add a second ORDER BY value (like time) to make it fixed. Use it with a subquery for "top N per group".

Example: Top 2 trades by amount per symbol (sample data: sample)

SELECT symbol, pos, transaction_id, quantity, rate, amount
FROM (SELECT symbol, transaction_id, quantity, rate, amount,
             row_number() OVER (PARTITION BY symbol ORDER BY amount DESC, transaction_id) AS pos
      FROM trade_log) t
WHERE pos <= 2
ORDER BY symbol, pos;

Result:

(8 rows)

Example: Page numbers for a list of forum posts: 3 posts per page (sample data: content-forum)

SELECT id, author, floor((row_number() OVER (ORDER BY created_at) - 1) / 3) + 1 AS page
FROM posts
ORDER BY created_at;

Result:

(8 rows)

rank

Syntax:

rank()
rank() OVER ([PARTITION BY ...] ORDER BY ...)

The place of the row in the window's order. Equal values get the same rank, and the next rank skips: 1, 1, 3.

• Returns: LONG.
• Notes: takes no argument (rank(close) is an error). Two rows tie when all their ORDER BY values are equal. Without ORDER BY, rows with the same time tie.

Example: Rank the day's trades of COAST by quantity (ties share a rank) (sample data: sample)

SELECT transaction_id, buyer, quantity,
       rank() OVER (ORDER BY quantity DESC) AS rank,
       dense_rank() OVER (ORDER BY quantity DESC) AS dense_rank,
       row_number() OVER (ORDER BY quantity DESC, transaction_id) AS row_number
FROM trade_log
WHERE date IN '2026-09-10' AND symbol = 'COAST'
ORDER BY quantity DESC, transaction_id;

Result:

(9 rows)

Example: Biggest gainer of each day (sample data: sample)

SELECT time, symbol, change_pct,
       rank() OVER (PARTITION BY time ORDER BY change_pct DESC) AS place
FROM (SELECT time, symbol, round((close - open) / open * 100, 2) AS change_pct
      FROM ochl_day
      WHERE time >= '2026-09-08') t
ORDER BY time, place;

Result:

(9 rows)

dense_rank

Syntax:

dense_rank()
dense_rank() OVER ([PARTITION BY ...] ORDER BY ...)

Like rank, but without gaps: equal values get the same rank, and the next rank is one more: 1, 1, 2.

• Returns: LONG.
• Notes: takes no argument. Use it for "price levels": every different price gets the next number.

Example: Price levels of VOLT's trades (sample data: sample)

SELECT transaction_id, rate,
       dense_rank() OVER (ORDER BY rate DESC) AS price_level,
       rank() OVER (ORDER BY rate DESC) AS rank
FROM trade_log
WHERE symbol = 'VOLT'
ORDER BY rate DESC, transaction_id;

Result:

(4 rows)

percent_rank

Syntax:

percent_rank()
percent_rank() OVER ([PARTITION BY ...] ORDER BY ...)

The place of the row as a part from 0 to 1: (rank - 1) / (rows - 1). The first row is 0, the last is 1. 0.9 means "90% of the other rows come before this one". It is the percentile rank.

• Returns: DOUBLE from 0 to 1.
• NULL: never NULL. A part with one row gives 0.
• Notes: takes no argument. Equal values (peers) get the same result, like rank. Without ORDER BY all rows are peers, so every row gets 0.

Example: Where does each COAST trade size stand? percent_rank, cume_dist and rank (sample data: sample)

SELECT transaction_id, quantity,
       rank() OVER (ORDER BY quantity) AS rank,
       round(percent_rank() OVER (ORDER BY quantity), 2) AS percent_rank,
       round(cume_dist() OVER (ORDER BY quantity), 2) AS cume_dist
FROM trade_log
WHERE symbol = 'COAST'
ORDER BY quantity, transaction_id;

Result:

(11 rows)

Example: How high are the last closes among all September closes of the same symbol? (100 = the highest) (sample data: sample)

SELECT time, symbol, close, percentile_rank
FROM (SELECT time, symbol, close,
             round(percent_rank() OVER (PARTITION BY symbol ORDER BY close) * 100) AS percentile_rank
      FROM ochl_day
      WHERE time >= '2026-09-01') t
WHERE time >= '2026-09-09'
ORDER BY symbol, time;

Result:

(6 rows)

cume_dist

Syntax:

cume_dist()
cume_dist() OVER ([PARTITION BY ...] ORDER BY ...)

The cumulative distribution: the part of the rows (from 0 to 1) whose value is at or before this row's value. 0.75 means "75% of the rows have this value or a smaller one".

• Returns: DOUBLE, more than 0 and at most 1.
• NULL: never NULL.
• Notes: takes no argument. Peers (equal values) all count, so the last peer's position is used: three trades of 20 among 11 trades after one smaller trade give 4 / 11. Without ORDER BY every row gets 1. See the example of percent_rank.

Example: What part of the app users signed up by each day? (sample data: fn-dates)

SELECT id, created_at, round(cume_dist() OVER (ORDER BY created_at) * 100) AS pct_of_users_so_far
FROM signups
ORDER BY created_at;

Result:

(10 rows)

ntile

Syntax:

ntile(n)
ntile(n) OVER ([PARTITION BY ...] ORDER BY ...)

Cuts the rows of each part into n groups of (almost) the same size, in window order, and gives the group number: 1 to n. ntile(4) gives quartiles, ntile(10) deciles.

• Returns: LONG from 1 to n.
• NULL: never NULL.
• Notes: when the rows do not divide evenly, the first groups get one row more: 11 rows in 3 groups are 4, 4 and 3 rows. Equal values can land in different groups (it counts positions, not values). n must be a constant whole number of 1 or more.

Example: Quartiles of COAST's September closes (1 = highest) (sample data: sample)

SELECT time, close, ntile(4) OVER (ORDER BY close DESC) AS quartile
FROM ochl_day
WHERE symbol = 'COAST' AND time >= '2026-09-01'
ORDER BY close DESC;

Result:

(8 rows)

Example: Three groups of trades by size: 11 rows are 4 + 4 + 3 (sample data: sample)

SELECT transaction_id, quantity, ntile(3) OVER (ORDER BY quantity, transaction_id) AS size_group
FROM trade_log
WHERE symbol = 'COAST'
ORDER BY quantity, transaction_id;

Result:

(11 rows)

Example: The number of groups must be 1 or more (sample data: sample; shows an error)

SELECT ntile(0) OVER (ORDER BY close) FROM ochl_day;

Result:

Error: line 1, column 8: ntile: the number of buckets must be a whole number (1 or more)

first_value

Syntax:

first_value(x)
first_value(x) OVER ([PARTITION BY ...] [ORDER BY ...] [ROWS | RANGE BETWEEN ... AND ...])

The value of x in the first row of the frame. With OVER (PARTITION BY symbol ORDER BY time) it is the first price of the symbol, on every row.

• Returns: the type of x.
• NULL: NULL when that row's value is NULL (NULL values are not skipped), or when the frame has no rows.
• Notes: it reads the frame. Without OVER, or without ORDER BY, the frame is the whole part. With ORDER BY and no frame, the frame runs from the first row to this row. ROWS 4 PRECEDING makes it the value of 4 rows ago (or the first row, near the start). The aggregate first gives one value per group instead.

Example: Change since the first day of the series, per symbol (sample data: sample)

SELECT time, symbol, close, start_close, round((close - start_close) / start_close * 100, 2) AS change_pct
FROM (SELECT time, symbol, close,
             first_value(close) OVER (PARTITION BY symbol ORDER BY time) AS start_close
      FROM ochl_day
      WHERE time >= '2026-09-01') t
WHERE time >= '2026-09-09'
ORDER BY symbol, time;

Result:

(6 rows)

Example: Open of each one-minute candle against the day's first open (sample data: sample)

SELECT time, open, first_value(open) AS day_open, round(open - first_value(open), 1) AS from_day_open
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10'
ORDER BY time DESC
LIMIT 3;

Result:

(3 rows)

last_value

Syntax:

last_value(x)
last_value(x) OVER ([PARTITION BY ...] [ORDER BY ...] [ROWS | RANGE BETWEEN ... AND ...])

The value of x in the last row of the frame.

• Returns: the type of x.
• NULL: NULL when that row's value is NULL (NULL values are not skipped), or when the frame has no rows.
• Notes: careful: with ORDER BY and no frame, the frame ends at this row, so last_value is just this row's value (the same as PostgreSQL). For the last value of the whole part, write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or leave out ORDER BY.

Example: The trap: without a frame, last_value is this row (sample data: sample)

SELECT time, close,
       last_value(close) OVER (ORDER BY time) AS this_row,
       last_value(close) OVER (ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_close,
       last_value(close) AS also_latest
FROM ochl_day
WHERE symbol = 'CITY' AND time >= '2026-09-06'
ORDER BY time;

Result:

(5 rows)

Example: How far each day's close is from the latest close, per symbol (sample data: sample)

SELECT time, symbol, close, latest, round(latest - close, 1) AS to_latest
FROM (SELECT time, symbol, close,
             last_value(close) OVER (PARTITION BY symbol ORDER BY time
                                     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest
      FROM ochl_day
      WHERE time >= '2026-09-08') t
ORDER BY symbol, time;

Result:

(9 rows)

nth_value

Syntax:

nth_value(x, n)
nth_value(x, n) OVER ([PARTITION BY ...] [ORDER BY ...] [ROWS | RANGE BETWEEN ... AND ...])

The value of x in row number n of the frame (1 is the first row).

• Returns: the type of x.
• NULL: NULL when the frame has fewer than n rows (so with ORDER BY and no frame, the first n - 1 rows are NULL), or when that value is NULL.
• Notes: nth_value(x, 1) is first_value(x). n must be a constant whole number of 1 or more.

Example: The second day's close, from the second day on (sample data: sample)

SELECT time, close, nth_value(close, 2) OVER (ORDER BY time) AS second_close
FROM ochl_day
WHERE symbol = 'CITY' AND time >= '2026-09-06'
ORDER BY time;

Result:

(5 rows)

Example: The second-biggest trade of each symbol, on every row (sample data: sample)

SELECT DISTINCT symbol, second_biggest
FROM (SELECT symbol,
             nth_value(amount, 2) OVER (PARTITION BY symbol ORDER BY amount DESC
                                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_biggest
      FROM trade_log) t
ORDER BY symbol;

Result:

(4 rows)

Example: Row numbers start at 1 (sample data: sample; shows an error)

SELECT nth_value(close, 0) OVER (ORDER BY time) FROM ochl_day;

Result:

Error: line 1, column 8: nth_value: the row number must be a whole number (1 or more)

Aggregates with OVER

Syntax:

count(*) OVER (...)    count(x) OVER (...)    sum(x) OVER (...)    avg(x) OVER (...)
min(x) OVER (...)      max(x) OVER (...)      first(x) OVER (...)  last(x) OVER (...)
median(x) OVER (...)   percentile(x, p) OVER (...)   stddev(x) OVER (...)   vwap(price, volume) OVER (...)

Every aggregate (aggregate functions, statistics) can be a window function, also the newer ones like count_if, corr or sharpe_ratio. It then gives a value for every row, computed over the row's window.

• NULL: NULL values are skipped, as in the plain aggregate.
• Notes: the window version works on the rows the SELECT gives: the table's rows, or the groups when there is a GROUP BY (then use aggregates inside it, as in avg(sum(qty)) OVER ()). first and last use the window's order.

Example: Distance from the symbol's average close (sample data: sample)

SELECT time, symbol, close,
       round(avg(close) OVER (PARTITION BY symbol), 2) AS avg_close,
       round(close - avg(close) OVER (PARTITION BY symbol), 2) AS diff
FROM ochl_day
WHERE time >= '2026-09-08'
ORDER BY symbol, time;

Result:

(9 rows)

Example: Moving 3-day VWAP (sample data: sample)

SELECT time, close, volume,
       round(vwap(close, volume) OVER (ORDER BY time ROWS 2 PRECEDING), 2) AS vwap_3d
FROM ochl_day
WHERE symbol = 'VOLT' AND time >= '2026-09-03'
ORDER BY time;

Result:

(6 rows)

A window function can sit next to GROUP BY. It runs over the grouped rows, after the aggregates, as in PostgreSQL.

Example: A window over groups: rank symbols by volume (sample data: sample)

SELECT symbol, sum(volume) AS volume,
       rank() OVER (ORDER BY sum(volume) DESC) AS place
FROM ochl_day
GROUP BY symbol
ORDER BY place;

Result:

(3 rows)

Example: The same idea with GROUP BY inside, window outside (sample data: sample)

SELECT day, turnover, sum(turnover) OVER (ORDER BY day) AS running_turnover
FROM (SELECT date AS day, sum(amount) AS turnover
      FROM trade_log
      GROUP BY date) t
ORDER BY day;

Result:

(2 rows)