📊 Master Metabase Business Intelligence

-- Customer lifetime value with detailed breakdown
SELECT 
    c.customer_id,
    c.first_name || ' ' || c.last_name as customer_name,
    c.signup_date,
    COUNT(DISTINCT o.order_id) as total_orders,
    SUM(o.total_amount) as lifetime_value,
    AVG(o.total_amount) as avg_order_value,
    -- Customer tier based on lifetime value
    CASE 
        WHEN SUM(o.total_amount) >= 10000 THEN 'Platinum'
        WHEN SUM(o.total_amount) >= 5000 THEN 'Gold'  
        WHEN SUM(o.total_amount) >= 1000 THEN 'Silver'
        ELSE 'Bronze'
    END as customer_tier,
    -- Recency analysis
    DATE_PART('days', CURRENT_DATE - MAX(o.order_date)) as days_since_last_order,
    -- Frequency analysis  
    CASE 
        WHEN DATE_PART('days', CURRENT_DATE - c.signup_date) > 0 THEN
            COUNT(DISTINCT o.order_id)::float / 
            (DATE_PART('days', CURRENT_DATE - c.signup_date) / 30.0)
        ELSE 0
    END as orders_per_month
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.signup_date >= '2023-01-01'
GROUP BY c.customer_id, c.first_name, c.last_name, c.signup_date
HAVING COUNT(DISTINCT o.order_id) > 0
ORDER BY lifetime_value DESC;

-- Cohort retention analysis
WITH monthly_cohorts AS (
    SELECT 
        customer_id,
        DATE_TRUNC('month', first_order_date) as cohort_month,
        DATE_TRUNC('month', order_date) as order_month
    FROM (
        SELECT 
            o.customer_id,
            o.order_date,
            MIN(o.order_date) OVER (PARTITION BY o.customer_id) as first_order_date
        FROM orders o
    ) t
),
cohort_data AS (
    SELECT 
        cohort_month,
        order_month,
        DATE_PART('month', AGE(order_month, cohort_month)) as month_number,
        COUNT(DISTINCT customer_id) as customers
    FROM monthly_cohorts
    GROUP BY cohort_month, order_month
),
cohort_sizes AS (
    SELECT 
        cohort_month,
        COUNT(DISTINCT customer_id) as cohort_size
    FROM monthly_cohorts
    WHERE cohort_month = order_month
    GROUP BY cohort_month
)
SELECT 
    cd.cohort_month,
    cs.cohort_size,
    cd.month_number,
    cd.customers as retained_customers,
    ROUND(cd.customers * 100.0 / cs.cohort_size, 2) as retention_rate
FROM cohort_data cd
JOIN cohort_sizes cs ON cd.cohort_month = cs.cohort_month
WHERE cd.month_number <= 12  -- First 12 months
ORDER BY cd.cohort_month, cd.month_number;

-- Revenue growth with moving averages and year-over-year comparison
WITH daily_revenue AS (
    SELECT 
        DATE(order_date) as date,
        SUM(total_amount) as daily_revenue
    FROM orders
    WHERE order_date >= '2022-01-01'
    GROUP BY DATE(order_date)
),
enriched_data AS (
    SELECT 
        date,
        daily_revenue,
        -- 7-day moving average
        AVG(daily_revenue) OVER (
            ORDER BY date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as ma_7day,
        -- 30-day moving average
        AVG(daily_revenue) OVER (
            ORDER BY date 
            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
        ) as ma_30day,
        -- Year-over-year comparison
        LAG(daily_revenue, 365) OVER (ORDER BY date) as revenue_previous_year,
        -- Month-over-month growth
        LAG(daily_revenue, 30) OVER (ORDER BY date) as revenue_previous_month
    FROM daily_revenue
)
SELECT 
    date,
    daily_revenue,
    ROUND(ma_7day, 2) as moving_avg_7day,
    ROUND(ma_30day, 2) as moving_avg_30day,
    revenue_previous_year,
    CASE 
        WHEN revenue_previous_year > 0 THEN
            ROUND((daily_revenue - revenue_previous_year) * 100.0 / revenue_previous_year, 2)
        ELSE NULL
    END as yoy_growth_percent,
    CASE 
        WHEN revenue_previous_month > 0 THEN
            ROUND((daily_revenue - revenue_previous_month) * 100.0 / revenue_previous_month, 2)
        ELSE NULL
    END as mom_growth_percent
FROM enriched_data
WHERE date >= '2023-01-01'
ORDER BY date;