WITH profile AS
(SELECT u.user_id,
DATE_TRUNC('month', MIN(event_time))::DATE AS dt
FROM tools_shop.users u
JOIN tools_shop.orders o ON u.user_id = o.user_id
JOIN tools_shop.events e ON u.user_id = e.user_id
GROUP BY 1),
sessions AS
(SELECT p.user_id,
DATE_TRUNC('month', event_time)::DATE AS session_dt
FROM tools_shop.events e
JOIN profile p ON p.user_id = e.user_id
GROUP BY 1,
2),
cohort_users_cnt AS
(SELECT dt,
COUNT(user_id) AS cohort_users_cnt
FROM profile
GROUP BY 1)
SELECT p.dt AS cohort_dt,
session_dt,
COUNT(p.user_id) AS users_cnt,
cohort_users_cnt,
ROUND(COUNT(p.user_id) * 100.0 / cohort_users_cnt, 2) AS retention_rate
FROM profile p
JOIN sessions s ON p.user_id = s.user_id
JOIN cohort_users_cnt cuc ON cuc.dt=p.dt
GROUP BY 1,
2,
4
ORDER BY 1, 2