rag/sample_templates/10_yoy_comparison.sql

-- Template: Year-over-Year Comparison
-- Pattern: Compare metrics across time periods
-- Use Case: Revenue growth, seasonal trends, period comparisons

WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', order_date) as month,
        EXTRACT(YEAR FROM order_date) as year,
        EXTRACT(MONTH FROM order_date) as month_num,
        SUM(total_amount) as revenue,
        COUNT(DISTINCT customer_id) as unique_customers
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date), 
             EXTRACT(YEAR FROM order_date),
             EXTRACT(MONTH FROM order_date)
)
SELECT 
    curr.month as current_month,
    curr.revenue as current_revenue,
    prev.revenue as previous_year_revenue,
    curr.revenue - prev.revenue as revenue_change,
    ROUND(((curr.revenue - prev.revenue) / prev.revenue * 100), 2) as growth_pct,
    curr.unique_customers as current_customers,
    prev.unique_customers as previous_year_customers
FROM monthly_revenue curr
LEFT JOIN monthly_revenue prev 
    ON curr.month_num = prev.month_num 
    AND curr.year = prev.year + 1
WHERE prev.revenue IS NOT NULL
ORDER BY curr.month DESC;

-- Key Concepts:
-- - DATE_TRUNC for period aggregation
-- - Self-join to compare same periods across years
-- - Percentage change calculation
-- - EXTRACT for getting year/month components
-- - Useful for trend analysis and forecasting