Window Frames: ROWS, RANGE, GROUPS, BETWEEN, EXCLUDE

-- Create time-series data for frame analysis
CREATE TABLE stock_prices (
    trade_date DATE,
    symbol VARCHAR(10),
    price DECIMAL(10,2),
    volume INT
);

INSERT INTO stock_prices VALUES
('2024-01-01', 'AAPL', 150.00, 1000000),
('2024-01-02', 'AAPL', 152.50, 1200000),
('2024-01-03', 'AAPL', 148.75, 800000),
('2024-01-04', 'AAPL', 155.00, 1500000),
('2024-01-05', 'AAPL', 153.25, 900000),
('2024-01-08', 'AAPL', 157.50, 1100000),
('2024-01-09', 'AAPL', 159.00, 1300000),
('2024-01-10', 'AAPL', 156.75, 700000),
('2024-01-11', 'AAPL', 160.00, 1400000),
('2024-01-12', 'AAPL', 162.50, 1600000);

-- ROWS: Fixed number of physical rows
SELECT 
    trade_date,
    price,
    volume,
    -- 3-day moving average (ROWS)
    AVG(price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3day,
    
    -- 5-day moving sum
    SUM(volume) OVER (
        ORDER BY trade_date
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) AS moving_sum_5day,
    
    -- Running average from start
    AVG(price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_avg,
    
    -- Entire dataset average
    AVG(price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS overall_avg
FROM stock_prices
ORDER BY trade_date;

-- RANGE: Logical value range
SELECT 
    trade_date,
    price,
    volume,
    -- Sum of prices within ±5 of current price
    SUM(price) OVER (
        ORDER BY price
        RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING
    ) AS sum_within_range,
    
    -- Count of stocks within 10% of current price
    COUNT(*) OVER (
        ORDER BY price
        RANGE BETWEEN price * 0.1 PRECEDING AND price * 0.1 FOLLOWING
    ) AS count_nearby_prices,
    
    -- Average of all rows with price <= current
    AVG(volume) OVER (
        ORDER BY price
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS avg_volume_lower_prices
FROM stock_prices
ORDER BY trade_date;

-- GROUPS: Peer group counting
SELECT 
    trade_date,
    price,
    volume,
    -- Sum of 1 peer group before and after
    SUM(price) OVER (
        ORDER BY price
        GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS sum_peer_groups,
    
    -- Count with GROUPS
    COUNT(*) OVER (
        ORDER BY volume
        GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS count_peer_groups,
    
    -- Average with GROUPS
    AVG(price) OVER (
        ORDER BY volume
        GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) AS avg_peer_groups
FROM stock_prices
ORDER BY trade_date;

-- Compare all three frame types
SELECT 
    trade_date,
    price,
    -- ROWS frame
    SUM(price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS sum_rows,
    
    -- RANGE frame (by value)
    SUM(price) OVER (
        ORDER BY price
        RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) AS sum_range,
    
    -- GROUPS frame
    SUM(price) OVER (
        ORDER BY price
        GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS sum_groups
FROM stock_prices
ORDER BY trade_date;

-- EXCLUDE CURRENT ROW
SELECT 
    trade_date,
    price,
    AVG(price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        EXCLUDE CURRENT ROW
    ) AS avg_excluding_current
FROM stock_prices
ORDER BY trade_date;

-- EXCLUDE GROUP (excludes all peers)
SELECT 
    trade_date,
    price,
    AVG(price) OVER (
        ORDER BY price
        GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        EXCLUDE GROUP
    ) AS avg_excluding_peers
FROM stock_prices
ORDER BY trade_date;

-- EXCLUDE TIES (excludes peers but keeps current)
SELECT 
    trade_date,
    price,
    AVG(price) OVER (
        ORDER BY price
        GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        EXCLUDE TIES
    ) AS avg_excluding_ties
FROM stock_prices
ORDER BY trade_date;

-- 7-day moving percentile
SELECT 
    trade_date,
    price,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_median_7day,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_q1_7day,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_q3_7day
FROM stock_prices
ORDER BY trade_date;

-- Check frame execution cost
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    trade_date,
    AVG(price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) AS moving_avg
FROM stock_prices;