Transform Your
MySQL On-Premise Solutions

Use Case:

"Advanced customer cohort analysis"

Solution:

                        
WITH RECURSIVE date_sequence AS (
    SELECT MIN(created_at) as date
    FROM customers
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 MONTH)
    FROM date_sequence
    WHERE date < CURRENT_DATE
),
customer_cohorts AS (
    SELECT 
        customer_id,
        DATE_FORMAT(created_at, '%Y-%m-01') as cohort_date,
        COUNT(*) OVER (
            PARTITION BY DATE_FORMAT(created_at, '%Y-%m-01')
        ) as cohort_size
    FROM customers
),
customer_activity AS (
    SELECT 
        cc.customer_id,
        cc.cohort_date,
        cc.cohort_size,
        o.order_date,
        o.total_amount,
        ROW_NUMBER() OVER (
            PARTITION BY cc.customer_id 
            ORDER BY o.order_date
        ) as order_sequence,
        TIMESTAMPDIFF(
            MONTH, 
            cc.cohort_date,
            DATE_FORMAT(o.order_date, '%Y-%m-01')
        ) as months_since_join
    FROM 
        customer_cohorts cc
        JOIN orders o ON cc.customer_id = o.customer_id
),
cohort_analysis AS (
    SELECT 
        cohort_date,
        months_since_join,
        COUNT(DISTINCT customer_id) as active_customers,
        cohort_size,
        ROUND(
            COUNT(DISTINCT customer_id) / 
            FIRST_VALUE(cohort_size) OVER (
                PARTITION BY cohort_date 
                ORDER BY months_since_join
            ) * 100,
            2
        ) as retention_rate,
        SUM(total_amount) as revenue,
        COUNT(*) as total_orders,
        ROUND(
            SUM(total_amount) / COUNT(DISTINCT customer_id),
            2
        ) as avg_customer_value
    FROM customer_activity
    GROUP BY 
        cohort_date,
        months_since_join,
        cohort_size
)
SELECT 
    cohort_date,
    months_since_join,
    active_customers,
    retention_rate,
    revenue,
    total_orders,
    avg_customer_value,
    LAG(retention_rate) OVER (
        PARTITION BY cohort_date 
        ORDER BY months_since_join
    ) as prev_period_retention,
    retention_rate - LAG(retention_rate) OVER (
        PARTITION BY cohort_date 
        ORDER BY months_since_join
    ) as retention_change
FROM cohort_analysis
ORDER BY 
    cohort_date,
    months_since_join;