devyx-sql-example-cte

devyx-sql-example-cte.sql

WITH high_value_customers AS (
	SELECT
		customer_id
		, SUM(total_revenue) AS total_revenue
	FROM transaction_details td
	LEFT JOIN customer_details cd
		ON td.card_id = cd.card_id
	GROUP BY 1
	ORDER BY 2 DESC
	HAVING total_revenue > 500
), new_customers AS (
	SELECT
		customer_id
		, MAX(DATEDIFF(CURDATE(), date_first_seen)) AS days_since_first_seen
	FROM customer_details
	GROUP BY 1
	HAVING days_since_first_seen < 90
)
SELECT
	hvc.customer_id
	, total_revenue
	, days_since_first_seen
FROM high_value_customers hvc 
INNER JOIN new_customers nc 
	ON hvc.customer_id = nc.customer_id