3_retention_analysis.sql

WITH last_purchase_date as (
    SELECT 
        customerkey,
        cleaned_name,
        orderdate,
        row_number() over(PARTITION BY customerkey ORDER BY orderdate DESC) AS rn,
        cohort_year,
        first_purchase_date
    FROM cohort_analysis
),
customer_statuses AS (
    SELECT 
        cohort_year,
        customerkey,
        cleaned_name,
        orderdate AS last_purchased_date,
        first_purchase_date,
        CASE 
            WHEN orderdate < ((SELECT MAX(orderdate) FROM sales) - INTERVAL '6 months') THEN 'churned'
            ELSE 'active'
        END customer_status
    FROM last_purchase_date
    WHERE 
        rn = 1 AND 
        first_purchase_date < ((SELECT MAX(orderdate) FROM sales) - INTERVAL '6 months')
)
SELECT 
    cohort_year,
    customer_status,
    count(customer_status) AS count_of_status,
    SUM(count(customer_status)) OVER(PARTITION BY cohort_year) AS total_customers,
    round(100*(count(customer_status)/SUM(count(customer_status)) OVER(PARTITION BY cohort_year)),2) AS percent_of_customers
FROM customer_statuses
GROUP BY 
    cohort_year,
    customer_status;