1_customer_segmentation.sql

WITH customer_total_ltv AS (
    SELECT 
        customerkey,
        cleaned_name,
        sum(net_revenue) AS total_ltv
    FROM cohort_analysis
    GROUP BY
        customerkey,
        cleaned_name
    ORDER BY customerkey 
), 
pcntl_cont AS (
    SELECT 
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_ltv) AS "25th_pcntl",
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_ltv) AS "75th_pcntl"
    FROM customer_total_ltv cl
), 
customer_category AS (
    SELECT
        customer_total_ltv.*,
        case
            WHEN total_ltv < "25th_pcntl" THEN '0_low_value'
            WHEN total_ltv >= "75th_pcntl" THEN '2_high_vale'
            ELSE '1_medium_value'
        END AS customer_segment
    FROM 
        pcntl_cont pc,
        customer_total_ltv  
)
SELECT 
    customer_segment,
    count(customerkey) AS NO_of_customers,
    sum(total_ltv) AS net_revenue_contribution,
    (sum(total_ltv)/count(customer_segment)) AS avg_ltv,
    round(100*(count(customerkey)/SUM(count(customerkey)) over()),2) AS percent_of_customers
FROM customer_category
GROUP BY 
    customer_segment
ORDER BY 
    customer_segment DESC;