rag/sample_templates/09_cohort_analysis.sql

-- Template: Cohort Analysis
-- Pattern: Group customers by acquisition period and track behavior
-- Use Case: First purchase cohorts, retention analysis, lifetime value

WITH first_purchases AS (
    SELECT 
        customer_id,
        MIN(order_date) as cohort_month,
        DATE_TRUNC('month', MIN(order_date)) as cohort_date
    FROM orders
    GROUP BY customer_id
),
customer_cohorts AS (
    SELECT 
        o.customer_id,
        o.order_date,
        o.total_amount,
        fp.cohort_date,
        DATE_TRUNC('month', o.order_date) as order_month,
        EXTRACT(YEAR FROM AGE(o.order_date, fp.cohort_date)) * 12 + 
        EXTRACT(MONTH FROM AGE(o.order_date, fp.cohort_date)) as months_since_first
    FROM orders o
    JOIN first_purchases fp ON o.customer_id = fp.customer_id
)
SELECT 
    cohort_date,
    months_since_first,
    COUNT(DISTINCT customer_id) as active_customers,
    SUM(total_amount) as cohort_revenue,
    AVG(total_amount) as avg_order_value
FROM customer_cohorts
GROUP BY cohort_date, months_since_first
ORDER BY cohort_date, months_since_first;

-- Key Concepts:
-- - Cohorts: Groups defined by shared characteristic (e.g., signup month)
-- - Track behavior over time relative to cohort start
-- - DATE_TRUNC for period grouping
-- - AGE function for time calculations
-- - Useful for retention and LTV analysis