Window Functions

-- OVER() without options: entire result set is the window
SELECT 
    name,
    price,
    AVG(price) OVER() AS avg_all_products,
    price - AVG(price) OVER() AS diff_from_avg
FROM products;

-- PARTITION BY: Create separate windows for each group
SELECT 
    category_id,
    name,
    price,
    AVG(price) OVER(PARTITION BY category_id) AS category_avg,
    price - AVG(price) OVER(PARTITION BY category_id) AS diff_from_category_avg
FROM products;

-- ORDER BY: Define order within the window for running calculations
SELECT 
    created_at,
    total,
    SUM(total) OVER(ORDER BY created_at) AS running_total,
    ROW_NUMBER() OVER(ORDER BY created_at) AS row_num
FROM orders;

-- Frame: Define exactly which rows to include
SELECT 
    date,
    revenue,
    -- 3-day moving average (current row + 2 preceding)
    AVG(revenue) OVER(
        ORDER BY date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3d,
    -- Sum of current row and next row
    SUM(revenue) OVER(
        ORDER BY date
        ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
    ) AS sum_with_next
FROM daily_sales;

-- ROWS: Physical rows
SELECT 
    date,
    revenue,
    -- Previous 2 physical rows + current
    SUM(revenue) OVER(
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS sum_3_rows
FROM daily_sales;

-- RANGE: Logical values (ties included)
SELECT 
    sale_date,
    amount,
    -- All rows with same date + preceding dates
    SUM(amount) OVER(
        ORDER BY sale_date
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales;

-- GROUPS: Groups of tied rows (PostgreSQL 11+)
SELECT 
    category,
    product,
    price,
    -- Average including 1 group before and 1 after
    AVG(price) OVER(
        ORDER BY category
        GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS avg_nearby_categories
FROM products;

-- Example with duplicate dates
-- Data: 2024-01-01 (x2), 2024-01-02 (x1)

-- ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
-- Row 1: Only itself
-- Row 2 (same date): Rows 1-2
-- Row 3: Rows 2-3

-- RANGE BETWEEN 1 PRECEDING AND CURRENT ROW  
-- Row 1: Rows 1-2 (same date = same range)
-- Row 2: Rows 1-2
-- Row 3: Rows 1-3 (value-based range)

-- Unique number for each row
SELECT 
    name,
    score,
    ROW_NUMBER() OVER(ORDER BY score DESC) AS row_num
FROM students;

-- Rank with gaps after ties
SELECT 
    name,
    score,
    RANK() OVER(ORDER BY score DESC) AS rank
FROM students;

-- Rank without gaps
SELECT 
    name,
    score,
    DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank
FROM students;

-- Divide into n buckets
SELECT 
    name,
    score,
    NTILE(4) OVER(ORDER BY score DESC) AS quartile
FROM students;

-- All together
SELECT 
    name,
    score,
    ROW_NUMBER() OVER(ORDER BY score DESC) AS row_num,
    RANK() OVER(ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank,
    NTILE(4) OVER(ORDER BY score DESC) AS quartile
FROM students;

-- Access previous row's value
SELECT 
    date,
    revenue,
    LAG(revenue) OVER(ORDER BY date) AS prev_day_revenue,
    LAG(revenue, 7) OVER(ORDER BY date) AS week_ago_revenue,
    LAG(revenue, 1, 0) OVER(ORDER BY date) AS prev_or_zero  -- Default if no prev
FROM daily_sales;

-- Access next row's value
SELECT 
    date,
    revenue,
    LEAD(revenue) OVER(ORDER BY date) AS next_day_revenue,
    LEAD(revenue, 7) OVER(ORDER BY date) AS next_week_revenue
FROM daily_sales;

-- Calculate day-over-day change
SELECT 
    date,
    revenue,
    revenue - LAG(revenue) OVER(ORDER BY date) AS dod_change,
    ROUND(
        (revenue - LAG(revenue) OVER(ORDER BY date)) * 100.0 
        / LAG(revenue) OVER(ORDER BY date), 
        2
    ) AS dod_pct_change
FROM daily_sales;

-- First value in the window
SELECT 
    category_id,
    name,
    price,
    FIRST_VALUE(name) OVER(
        PARTITION BY category_id 
        ORDER BY price DESC
    ) AS most_expensive_in_category,
    FIRST_VALUE(price) OVER(
        PARTITION BY category_id 
        ORDER BY price DESC
    ) AS max_price_in_category
FROM products;

-- Last value in the window
-- ⚠️ Requires proper frame definition!
SELECT 
    category_id,
    name,
    price,
    LAST_VALUE(name) OVER(
        PARTITION BY category_id 
        ORDER BY price DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS cheapest_in_category
FROM products;

-- Get the Nth value in the window
SELECT 
    category_id,
    name,
    price,
    NTH_VALUE(name, 2) OVER(
        PARTITION BY category_id 
        ORDER BY price DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_most_expensive
FROM products;