SQL Window Queries for Rolling and Cross-Sectional Financial Calculations

Hello. In the previous lesson, you used pandas and NumPy to compute grouped returns, rolling estimates, cross-sectional normalizations, and lagged portfolio P&L without explicit Python loops. SQL window functions express many of those same operations directly in a database.

This lesson focuses on writing PostgreSQL-style window queries for market data. You will calculate returns with LAG, construct trailing rolling statistics with explicit frames, and rank or bucket assets cross-sectionally by date. The central discipline remains unchanged: partition observations correctly, order them deterministically, and prevent a calculation from using information that would not yet have been available.

---

Window functions preserve the market-data table

Assume a validated daily-price table named daily_prices:

trading_date	symbol	adj_close	volume
2024-01-02	INFY	1450.20	5,800,000
2024-01-03	INFY	1461.80	6,100,000
2024-01-02	RELIANCE	2580.40	3,200,000

For this lesson, assume there is at most one row for each (symbol, trading_date) pair and that adj_close has already been audited for duplicates, missingness, and corporate-action artifacts.

A GROUP BY query reduces rows. For example:

SELECT
    symbol,
    AVG(adj_close) AS average_price
FROM daily_prices
GROUP BY symbol;

This returns one row per symbol. That is useful for summaries, but not for feature engineering, because you have lost the original date-level observations.

A window function calculates over a related set of rows while retaining every input row. Its general form is:

function_name(expression) OVER (
    PARTITION BY grouping_column
    ORDER BY ordering_column
    frame_definition
)

The clauses answer three separate questions:

Clause	Question answered in financial data
PARTITION BY	Which observations belong together? Usually each symbol, date, sector, or portfolio.
ORDER BY inside OVER	In what sequence should observations be interpreted? Usually trading time.
ROWS BETWEEN ...	Which ordered observations are included for this row? For example, the last 20 observations.

PARTITION BY and the frame are optional in SQL syntax, but their financial meaning should always be deliberate.

For example, this adds each stock’s maximum observed adjusted close to every price row:

SELECT
    trading_date,
    symbol,
    adj_close,
    MAX(adj_close) OVER (
        PARTITION BY symbol
    ) AS maximum_price_in_sample
FROM daily_prices
ORDER BY symbol, trading_date;

The output still has one row per date and symbol. The MAX is repeated for each row in that symbol’s partition.

A key warning: maximum_price_in_sample uses the entire history available to the query. It can be a descriptive statistic, but it is not a valid historical trading feature for an earlier date because it includes later prices.

---

Temporal windows: previous prices, returns, and trailing estimates

For a daily return, each symbol must be isolated from every other symbol and ordered in time. LAG retrieves a value from an earlier row in that ordered partition.

$$r_{i,t}=\frac{P_{i,t}}{P_{i,t-1}}-1$$

A clean implementation uses a common table expression, or CTE. The first CTE computes the prior close; the outer query computes the return from it.

WITH price_lags AS (
    SELECT
        trading_date,
        symbol,
        adj_close,
        LAG(adj_close) OVER (
            PARTITION BY symbol
            ORDER BY trading_date
        ) AS previous_adj_close
    FROM daily_prices
)
SELECT
    trading_date,
    symbol,
    adj_close,
    previous_adj_close,
    adj_close / NULLIF(previous_adj_close, 0) - 1.0
        AS simple_return
FROM price_lags
ORDER BY symbol, trading_date;

NULLIF(previous_adj_close, 0) returns NULL rather than attempting division by zero. The first price for every symbol has no earlier observation, so its simple_return should naturally be NULL.

The CTE matters because you generally cannot reuse a SELECT alias such as previous_adj_close in another expression in the same SELECT list. More importantly, it makes the calculation auditable: first establish the correct predecessor, then transform it into a return.

What LAG does — and does not — guarantee

LAG means “previous stored row,” not necessarily “previous calendar day.” If daily_prices contains exactly one validated observation for every exchange session, that is usually what you want. But if the source is missing a trading day for one symbol, LAG can silently compare a price with an older observation and produce a multi-day return.

That is why the data checks from earlier lessons come first. In production research, retain the exchange calendar or a complete asset-date panel so that unexpected gaps can be detected explicitly.

Also, the ORDER BY inside OVER defines the calculation order. The final ORDER BY symbol, trading_date only controls how results are displayed. SQL does not guarantee a result’s presentation order without that final clause.

Rolling means and volatility

A moving average is an aggregate calculated over a frame that moves with the current row. A trailing 20-observation mean that includes the current return is:

AVG(simple_return) OVER (
    PARTITION BY symbol
    ORDER BY trading_date
    ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
) AS return_mean_20

There are 20 rows in that frame: 19 prior rows plus the current row.

For a predictive feature intended to be known before the return on the current row, exclude the current row:

ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING

This frame includes exactly 20 earlier rows. The following query produces a prior-only trailing mean and sample volatility for each symbol.

WITH price_lags AS (
    SELECT
        trading_date,
        symbol,
        adj_close,
        LAG(adj_close) OVER (
            PARTITION BY symbol
            ORDER BY trading_date
        ) AS previous_adj_close
    FROM daily_prices
),
returns AS (
    SELECT
        trading_date,
        symbol,
        adj_close / NULLIF(previous_adj_close, 0) - 1.0
            AS simple_return
    FROM price_lags
)
SELECT
    trading_date,
    symbol,
    simple_return,
    AVG(simple_return) OVER trailing_20 AS mean_return_prior_20,
    STDDEV_SAMP(simple_return) OVER trailing_20 AS volatility_prior_20
FROM returns
WINDOW trailing_20 AS (
    PARTITION BY symbol
    ORDER BY trading_date
    ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING
)
ORDER BY symbol, trading_date;

STDDEV_SAMP is PostgreSQL’s sample standard deviation. Early rows have fewer than 20 predecessors; depending on the function, SQL may return a statistic from the smaller available frame or NULL when it lacks enough observations. A production signal should state an explicit minimum-history policy.

For example, add a count and only use volatility when it has 20 non-null returns:

COUNT(simple_return) OVER trailing_20 AS prior_return_count

Then filter or flag records with prior_return_count < 20 in an outer query.

The picture also highlights an important distinction: a frame based on ROWS counts records, not elapsed calendar days. ROWS BETWEEN 19 PRECEDING AND CURRENT ROW means 20 stored observations. That commonly corresponds to 20 trading sessions in a clean daily table, but it is not automatically “the past 20 calendar days.”

---

Cross-sectional windows: compare assets on the same date

Time-series windows partition by symbol and order by date. Cross-sectional calculations reverse that perspective: they partition by trading_date, comparing all eligible assets on one date.

Suppose daily_returns is a table or CTE containing:

trading_date	symbol	simple_return
2024-01-03	INFY	0.0080
2024-01-03	RELIANCE	-0.0024
2024-01-03	HDFCBANK	0.0041

The cross-sectional average return is:

SELECT
    trading_date,
    symbol,
    simple_return,
    AVG(simple_return) OVER (
        PARTITION BY trading_date
    ) AS cross_sectional_mean_return
FROM daily_returns
ORDER BY trading_date, symbol;

To calculate an asset’s return relative to that day’s cross-sectional average, repeat the window expression:

SELECT
    trading_date,
    symbol,
    simple_return,
    simple_return
        - AVG(simple_return) OVER (
            PARTITION BY trading_date
        ) AS demeaned_return
FROM daily_returns
ORDER BY trading_date, symbol;

This resembles pandas code such as:

df["demeaned_return"] = (
    df["simple_return"]
    - df.groupby("trading_date")["simple_return"].transform("mean")
)

The SQL window function and pandas transform share the essential property that the group statistic is returned to every original row.

Cross-sectional ranks

Ranking is central to factor research. For example, a momentum or value signal can be ranked among stocks every day before positions are selected.

SELECT
    trading_date,
    symbol,
    momentum_signal,
    RANK() OVER (
        PARTITION BY trading_date
        ORDER BY momentum_signal DESC
    ) AS momentum_rank
FROM daily_signals
WHERE momentum_signal IS NOT NULL
ORDER BY trading_date, momentum_rank, symbol;

With descending order, rank 1 is the largest signal on that date.

The choice of ranking function determines how ties behave:

Function	Tie behavior	Typical use
ROW_NUMBER()	Forces a unique sequence, even for tied values	Selecting exactly $N$ assets with a deterministic tie-breaker
RANK()	Equal values share a rank; later rank numbers may be skipped	Reporting relative standing where ties should be visible
DENSE_RANK()	Equal values share a rank; no later ranks are skipped	Group labels based on distinct values
NTILE(k)	Divides rows into approximately equal-sized buckets	Quintile or decile portfolio formation

For example, construct five approximately equal cross-sectional buckets:

SELECT
    trading_date,
    symbol,
    value_signal,
    NTILE(5) OVER (
        PARTITION BY trading_date
        ORDER BY value_signal DESC, symbol
    ) AS value_quintile
FROM daily_signals
WHERE value_signal IS NOT NULL
ORDER BY trading_date, value_quintile, symbol;

Here, value_quintile = 1 denotes the high-signal bucket. Adding symbol makes the result deterministic when two signals have the same value. It also means tied values can be split across buckets; that may be acceptable for a backtest specification, but it should be documented.

Cross-sectional results depend critically on the eligible universe. If delisted firms, suspended securities, or stocks without usable signals are excluded, do so according to a rule that could have been known on that date. Never define a historical universe using information available only at the end of the sample.

---

Standardizing a daily cross-section

A rank is robust to the magnitude of a signal, but sometimes you want a standardized score. For daily signal $s_{i,t}$, a cross-sectional z-score is:

$$z_{i,t}=\frac{s_{i,t}-{\overline{s}}_t}{{\hat{\sigma }}_t}$$

In PostgreSQL, calculate the mean and standard deviation in one CTE, then calculate the z-score in the outer query:

WITH daily_stats AS (
    SELECT
        trading_date,
        symbol,
        alpha_signal,
        AVG(alpha_signal) OVER (
            PARTITION BY trading_date
        ) AS daily_signal_mean,
        STDDEV_SAMP(alpha_signal) OVER (
            PARTITION BY trading_date
        ) AS daily_signal_std
    FROM daily_signals
    WHERE alpha_signal IS NOT NULL
)
SELECT
    trading_date,
    symbol,
    alpha_signal,
    (alpha_signal - daily_signal_mean)
        / NULLIF(daily_signal_std, 0) AS alpha_zscore
FROM daily_stats
ORDER BY trading_date, symbol;

NULLIF(daily_signal_std, 0) prevents an invalid division on a date where every eligible asset has exactly the same signal. A NULL z-score is preferable to a fabricated number: it tells downstream code that no cross-sectional dispersion existed.

These features can support a portfolio rule, but they are not a backtest by themselves. If alpha_zscore is calculated at the close on date $t$, it must be converted to positions that earn returns only after that decision time. In database terms, you often calculate features in one CTE or table, then join them to a later execution or return record under an explicit timing convention.

---

Research safeguards and practical SQL habits

Window functions are concise, but the same concise syntax can hide serious errors. Use this checklist before trusting a market-data query.

1. Check the partition

Ask: “Could information cross this boundary?”

• Return or rolling-volatility calculation: PARTITION BY symbol
• Daily ranking of stocks: PARTITION BY trading_date
• Sector-relative score on a date: PARTITION BY trading_date, sector
• Whole-portfolio cumulative metric: potentially no partition

A missing PARTITION BY symbol in a return query can make the first row of one stock use the final price of another stock as its predecessor.

2. Make ordering deterministic

For end-of-day data, ORDER BY trading_date is usually sufficient only if (symbol, trading_date) is unique.

For intraday data, use an ordering key that resolves ties, such as:

ORDER BY event_timestamp, sequence_number

Do not depend on accidental storage order. If two rows tie under the window’s ORDER BY, a ROWS frame can be nondeterministic.

3. Specify the frame explicitly

For ordered aggregates, avoid relying on a database’s default frame behavior. Write the intended frame:

ROWS BETWEEN 19 PRECEDING AND CURRENT ROW

or, for a prior-only feature:

ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING

This makes peer-row and look-ahead choices visible to a reviewer.

4. Do not truncate required history too early

Suppose you need returns beginning on 1 January 2024. If you filter raw prices to 2024 before computing LAG, the first retained day lacks its legitimate predecessor from 2023.

Instead:

1. Compute LAG on a history range that includes the needed lookback.
2. Compute returns or rolling features.
3. Apply the final reporting-period filter in an outer query.

The same principle applies to a 252-day volatility feature: retrieve at least 252 preceding valid observations before the evaluation period.

5. Treat nulls as evidence, not zeros

Most SQL aggregates ignore NULL values. This can be useful, but it can also conceal insufficient history or missing returns. Include COUNT(...) OVER (...) when a minimum sample size matters, and decide whether to exclude, flag, or defer an observation.

6. Support the common access pattern with an index

For large tables, window calculations often benefit from an index aligned with the partition and ordering keys:

CREATE INDEX IF NOT EXISTS daily_prices_symbol_date_idx
ON daily_prices (symbol, trading_date);

This does not eliminate the cost of every sort or rolling calculation, but it is a sensible starting design for repeated per-symbol chronological queries. Use EXPLAIN ANALYZE before making performance claims.

---

Key takeaways

SQL window functions calculate across related rows while preserving the original market-data row structure.

• Use PARTITION BY symbol with chronological ORDER BY trading_date for asset-level time-series calculations.
• Use LAG to obtain prior observations, then calculate returns in an outer query or CTE.
• Use explicit ROWS BETWEEN frames for rolling features; ROWS counts observations, not calendar time.
• Exclude the current row from a feature frame when it must be available before the current row’s outcome.
• Use PARTITION BY trading_date for cross-sectional means, standardization, ranks, and quantile buckets.
• RANK, DENSE_RANK, ROW_NUMBER, and NTILE differ mainly in their handling of ties and bucket sizes.
• Validate uniqueness, missing observations, universe membership, and timing before treating a compact SQL query as research evidence.

Next, you will visualize return distributions, dependence, and volatility regimes. The return panels and rolling features built so far will become diagnostic plots that reveal skewness, heavy tails, correlation structure, and changes in market behavior.