rag/sample_templates/05_top_n_per_group.sql

-- Template: Top N Records Per Group
-- Pattern: Get top performers within each category/partition
-- Use Case: Best sellers per category, top customers per region

WITH ranked_products AS (
    SELECT 
        p.product_id,
        p.name,
        p.category,
        p.price,
        COUNT(oi.order_id) as times_ordered,
        SUM(oi.quantity) as total_quantity_sold,
        SUM(oi.quantity * oi.unit_price) as total_revenue,
        ROW_NUMBER() OVER (
            PARTITION BY p.category 
            ORDER BY SUM(oi.quantity * oi.unit_price) DESC
        ) as revenue_rank
    FROM products p
    LEFT JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.name, p.category, p.price
)
SELECT 
    category,
    product_id,
    name,
    total_revenue,
    total_quantity_sold,
    revenue_rank
FROM ranked_products
WHERE revenue_rank <= 3
ORDER BY category, revenue_rank;

-- Key Concepts:
-- - CTE for better readability
-- - PARTITION BY category to rank within each group
-- - Filter WHERE rank <= N to get top N
-- - Useful for identifying best performers in each segment