uid_cohort_metrics.sql

SELECT
	dc.CustomerId,
	dc.AcquisitionDate,
	DATEDIFF(ac.ActivityDate, dc.AcquisitionDate) AS DaySinceAcquisition,
	SUM(ac.D1Purchases) A D1Purchases
FROM dim_cust_purchaser dc
LEFT OUTER JOIN (
	SELECT
		CustomerId, 
		ActivityDate, 
		SUM(D1Quantity) AS D1Purchases
	FROM agg_daily_activity
	WHERE 
		ActivityType IN ('purchase')
		AND ActivityDate >= '<MIN_DATE>'
	GROUP BY 1,2
) ac 
	ON dc.CustomerId = ac.CustomerId 
	AND dc.AcquisitionDate <= ac.ActivityDate
WHERE 
	dc.AcquisitionDate >= '<MIN_DATE>'
GROUP BY 1,2,3