3_customer_retention.sql

-- active and churned customers 
-- trending year 
-- classify churned vs active 
WITH customer_last_purchase AS (
	SELECT 
		customerkey,
		cohort_year,
		cleaned_name,
		orderdate,
		ROW_NUMBER() OVER(PARTITION BY customerkey ORDER BY orderdate DESC) AS rn,
		first_purchase_date
	FROM cohort_analysis_second
), churned_customers AS(
	SELECT 
		customerkey,
		cohort_year,
		cleaned_name,
	--	first_purchase_date,
		orderdate AS last_purchase_date,
		CASE 
			WHEN orderdate < (SELECT MAX(orderdate) FROM sales) - INTERVAL '6 months' THEN 'Churned'
			ELSE 'Active'
		END AS customer_status
	FROM customer_last_purchase 
	WHERE rn = 1
		AND first_purchase_date < (SELECT MAX(orderdate) FROM sales) - INTERVAL '6 months' -- there might be customers who have only used the site once so this might be classified as active causing a bias 
)
SELECT 
	cohort_year,
	customer_status,
	COUNT(customerkey) AS num_customers,
	SUM(COUNT(customerkey)) OVER(PARTITION BY cohort_year) AS total_customers,
	ROUND(COUNT(customerkey)/SUM(COUNT(customerkey)) OVER(PARTITION BY cohort_year),2) AS status_percentage
FROM churned_customers
GROUP BY customer_status ,cohort_year
	
	
--  from last data point 6 months before 2024-04-20
--SELECT MAX(orderdate)FROM sales -- using this instead of hardcore 2024-04-20