WITH profile AS
(SELECT customer_id
,MIN(CAST(DATE_TRUNC('week', order_date) AS TIMESTAMP)) AS dt
,COUNT(*) OVER (PARTITION BY MIN(CAST(DATE_TRUNC('week', order_date) AS TIMESTAMP))) AS cohort_users_cnt
FROM northwind.orders
GROUP BY customer_id
ORDER BY 2),
sessions AS
(SELECT customer_id,
CAST(DATE_TRUNC('week', order_date) AS TIMESTAMP) AS session_date
FROM northwind.orders
GROUP BY customer_id, order_date
ORDER BY 2,1)
SELECT p.dt AS cohort_dt,
s.session_date AS purchase_date,
COUNT(p.customer_id) AS users_cnt,
p.cohort_users_cnt,
ROUND(COUNT(p.customer_id) * 100.0 / p.cohort_users_cnt, 2) AS retention_rate
FROM profile p
JOIN sessions s ON p.customer_id = s.customer_id
GROUP BY 1,2,4
ORDER BY 1,2
