rag/sample_templates/03_window_functions.sql

-- Template: Window Functions for Ranking
-- Pattern: Rank records within partitions
-- Use Case: Top N per category, rankings, row numbers

-- Example 1: Rank customers by total orders in each city
SELECT 
    customer_id,
    name,
    city,
    total_orders,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY total_orders DESC) as row_num,
    RANK() OVER (PARTITION BY city ORDER BY total_orders DESC) as rank,
    DENSE_RANK() OVER (PARTITION BY city ORDER BY total_orders DESC) as dense_rank
FROM (
    SELECT 
        c.customer_id,
        c.name,
        c.city,
        COUNT(o.order_id) as total_orders
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.name, c.city
) customer_metrics;

-- Example 2: Top 3 customers per city
SELECT *
FROM (
    SELECT 
        customer_id,
        name,
        city,
        total_orders,
        ROW_NUMBER() OVER (PARTITION BY city ORDER BY total_orders DESC) as rn
    FROM customer_metrics
) ranked
WHERE rn <= 3;

-- Key Concepts:
-- - ROW_NUMBER(): Unique sequential number (1,2,3,4...)
-- - RANK(): Same rank for ties, skips next rank (1,2,2,4...)
-- - DENSE_RANK(): Same rank for ties, no gaps (1,2,2,3...)
-- - PARTITION BY: Restart ranking within each group
-- - ORDER BY: Defines ranking order within partition