Window Functions & Time-Series Operations

LAG and LEAD Functions

Access previous (LAG) or next (LEAD) row values within a group.

LAG - Previous Row Value

-- Get previous day's price
UPDATE prev_price: LAG(PRICE, 1)
FROM prices
GROUP BY TICKER;

-- Calculate daily return
UPDATE
    prev_price: LAG(PRICE, 1),
    daily_return: PRICE / LAG(PRICE, 1) - 1
FROM prices
GROUP BY TICKER;

-- Get price from 5 days ago
UPDATE price_5d_ago: LAG(PRICE, 5)
FROM prices
GROUP BY TICKER;

LEAD - Next Row Value

-- Get next day's price
UPDATE next_price: LEAD(PRICE, 1)
FROM prices
GROUP BY TICKER;

-- Forward return calculation
UPDATE forward_return: LEAD(PRICE, 1) / PRICE - 1
FROM prices
GROUP BY TICKER;

Cumulative Functions

CUMSUM - Cumulative Sum

-- Running total of prices
UPDATE cumulative_sum: CUMSUM(PRICE)
FROM prices
GROUP BY TICKER;

-- Cumulative return
UPDATE cumulative_return: CUMSUM(daily_return)
FROM prices
GROUP BY TICKER;

CUMMAX - Cumulative Maximum

-- Running maximum of prices
UPDATE cumulative_max: CUMMAX(PRICE)
FROM prices
GROUP BY TICKER;

-- Track high water mark
UPDATE highest_volume: CUMMAX(VOLUME)
FROM prices
GROUP BY TICKER;

CUMPROD - Cumulative Product

-- Calculate split adjustment factor
UPDATE split_adj: CUMPROD(split_ratio)
FROM splits
GROUP BY TICKER;

-- Calculate dividend adjustment
UPDATE dvd_adj: CUMPROD(dvd_adj)
FROM dividends
GROUP BY TICKER;

Moving Average Functions

EMA - Exponential Moving Average

-- 20-day exponential moving average
-- alpha = 2/(N+1) = 2/21 = 0.095
UPDATE price_ema20: EMA(PRICE, 0.095)
FROM prices
GROUP BY TICKER;

-- 50-day EMA (alpha = 2/51 = 0.039)
UPDATE price_ema50: EMA(PRICE, 0.039)
FROM prices
GROUP BY TICKER;

-- Volume EMA with custom alpha
UPDATE volume_ema: EMA(VOLUME, 0.333)
FROM prices
GROUP BY TICKER;

FILL - Forward Fill Missing Values

-- Fill missing prices with last known value
UPDATE PRICE: FILL(PRICE)
FROM prices
GROUP BY TICKER;

-- Fill multiple columns
UPDATE
    PRICE: FILL(PRICE),
    VOLUME: FILL(VOLUME)
FROM prices
GROUP BY TICKER, DATE;

Moving Statistical Functions

MCORR - Moving Correlation

-- Calculate 20-day rolling correlation between two stocks
CREATE TABLE stock_pair AS (
    SELECT
        a.DATE,
        a.PRICE as price_a,
        b.PRICE as price_b
    FROM (SELECT * FROM prices WHERE TICKER = 'AAPL') a
    INNER JOIN (SELECT * FROM prices WHERE TICKER = 'MSFT') b
        ON a.DATE = b.DATE
);

-- Calculate returns first
UPDATE
    return_a: price_a / LAG(price_a, 1) - 1,
    return_b: price_b / LAG(price_b, 1) - 1
FROM stock_pair;

-- Calculate 20-day rolling correlation
UPDATE rolling_corr_20: MCORR(return_a, return_b, 20)
FROM stock_pair;

-- Calculate multiple window sizes
UPDATE
    rolling_corr_5: MCORR(return_a, return_b, 5),
    rolling_corr_20: MCORR(return_a, return_b, 20),
    rolling_corr_60: MCORR(return_a, return_b, 60)
FROM stock_pair;

MCOVAR - Moving Covariance

-- Calculate 20-day rolling covariance
UPDATE rolling_covar_20: MCOVAR(return_a, return_b, 20)
FROM stock_pair;

-- Calculate multiple window sizes for covariance
UPDATE
    rolling_covar_5: MCOVAR(return_a, return_b, 5),
    rolling_covar_20: MCOVAR(return_a, return_b, 20),
    rolling_covar_60: MCOVAR(return_a, return_b, 60)
FROM stock_pair;

-- Relationship between moving correlation and covariance
-- MCOVAR(X,Y,N) = MCORR(X,Y,N) * MSTD(X,N) * MSTD(Y,N)
UPDATE
    rolling_covar: MCOVAR(return_a, return_b, 20),
    rolling_corr: MCORR(return_a, return_b, 20),
    rolling_std_a: MSTD(return_a, 20),
    rolling_std_b: MSTD(return_b, 20)
FROM stock_pair;

Ranking Functions

WRANK - Weighted Rank

-- Rank into 10 quantiles (deciles)
UPDATE decile: TO_INTEGER(WRANK(PRICE, 10))
FROM prices;

-- Rank into 5 quintiles
UPDATE quintile: TO_INTEGER(WRANK(PRICE, 5))
FROM prices;

-- Create cross-sectional ranks by date
UPDATE price_rank: WRANK(PRICE, 100)
FROM prices
GROUP BY DATE;

FIRST and LAST

-- Get first value in group
SELECT
    TICKER,
    FIRST(PRICE) as first_price,
    FIRST(DATE) as first_date
FROM prices
GROUP BY TICKER;

-- Get last value in group
SELECT
    TICKER,
    LAST(PRICE) as last_price,
    LAST(DATE) as last_date
FROM prices
GROUP BY TICKER;

-- Both first and last
UPDATE
    start_price: FIRST(PRICE),
    end_price: LAST(PRICE)
FROM prices
GROUP BY TICKER;

Advanced Patterns

Multiple Window Functions

-- Complex time-series features
UPDATE
    prev_price: LAG(PRICE, 1),
    prev_return: LAG(PRICE, 1) / LAG(PRICE, 2) - 1,
    price_ema: EMA(PRICE, 0.1),
    cum_return: CUMPROD(1 + daily_return) - 1,
    rank_today: WRANK(PRICE, 10)
FROM prices
GROUP BY TICKER;

Nested Window Operations

-- Calculate momentum indicators
UPDATE momentum: PRICE / LAG(PRICE, 20) - 1
FROM prices
GROUP BY TICKER;

-- Then rank the momentum
UPDATE momentum_rank: WRANK(momentum, 10)
FROM prices
GROUP BY DATE;