rag/sample_templates/01_customer_aggregation.sql

-- Template: Customer Order Aggregation
-- Pattern: Aggregate metrics by customer dimension
-- Use Case: Calculate total order value, count, average per customer

SELECT 
    c.customer_id,
    c.name,
    c.email,
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as total_spent,
    AVG(o.total_amount) as avg_order_value,
    MIN(o.order_date) as first_order_date,
    MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email
ORDER BY total_spent DESC;

-- Key Concepts:
-- - LEFT JOIN to include customers with no orders
-- - Multiple aggregations (COUNT, SUM, AVG, MIN, MAX)
-- - GROUP BY all non-aggregated columns
-- - ORDER BY to rank results