Window Functions

FUNCTION() OVER (
    PARTITION BY column   -- Optional: divide into groups
    ORDER BY column       -- Optional: order within partition
)

-- Number each row
SELECT 
    name,
    total,
    ROW_NUMBER() OVER (ORDER BY total DESC) AS rank
FROM orders;

-- Number within each customer
SELECT 
    user_id,
    total,
    ROW_NUMBER() OVER (
        PARTITION BY user_id 
        ORDER BY created_at DESC
    ) AS order_num
FROM orders;

-- Get most recent order per user
SELECT * FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY created_at DESC
        ) AS rn
    FROM orders
) WHERE rn = 1;

-- Compare ranking functions
SELECT 
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,  -- 1,2,3,4
    RANK() OVER (ORDER BY score DESC) AS rank,           -- 1,2,2,4 (gaps)
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense     -- 1,2,2,3 (no gaps)
FROM players;

-- Running total of orders
SELECT 
    created_at,
    total,
    SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders;

-- Running total per customer
SELECT 
    user_id,
    created_at,
    total,
    SUM(total) OVER (
        PARTITION BY user_id 
        ORDER BY created_at
    ) AS customer_running_total
FROM orders;

-- Compare to previous row
SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_sales;

-- Compare to next row
SELECT 
    name,
    score,
    LEAD(score) OVER (ORDER BY score DESC) AS next_lower_score
FROM players;