1_customer_segmentation.sql

WITH customer_ltv AS (
	SELECT 
		customerkey,
		cleaned_name,
		SUM(total_net_revenue) AS total_ltv
	FROM cohort_analysis_second
	GROUP BY
		customerkey,
		cleaned_name
),customer_percentile AS (
--percentile continuous function 
	SELECT 
		PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY total_ltv) AS ltv_25th_percentile,
		PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY total_ltv) AS ltv_75th_percentile
	FROM customer_ltv
), segment_value AS(
	SELECT
		c.*,
		CASE 
			WHEN c.total_ltv < cp.ltv_25th_percentile THEN '1 - Low-Value'
			WHEN c.total_ltv <= cp.ltv_75th_percentile THEN '2 - Mid-Value'
			ELSE '3 - High-Value'
		END AS customer_segment
	FROM customer_ltv c,
		customer_percentile cp
)
SELECT 
	customer_segment,
	SUM(total_ltv) AS total_ltv,
	-- avergage ltv in a certain segment 
	COUNT(customerkey) AS customer_count,
	-- average customer value 
	SUM(total_ltv)/COUNT(customerkey) AS avg_ltv
FROM segment_value
GROUP BY
	customer_segment
ORDER BY
	customer_segment DESC
	
-- customer segmentation from our cleaned cohort
-- bucket customers to high - low value