devyx-example-not-using-cte

devyx-example-not-using-cte.sql

SELECT
	hvc.customer_id
	, total_revenue
	, days_since_first_seen
FROM (
	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
) hvc
INNER JOIN (
	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
) nc
	ON hvc.customer_id = nc.customer_id