Untitled
unknown
sql
2 months ago
628 B
35
Indexable
Never
WITH profiles AS (SELECT user_id, CAST(DATE_TRUNC('month', created_at) AS date) AS cohort_start, (COUNT(user_id) OVER(PARTITION BY (CAST(DATE_TRUNC('month', created_at) AS date)))) AS cohort_count FROM tools_shop.users) SELECT EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', o.paid_at)::date, DATE_TRUNC('month', p.cohort_start)::date)) AS lifetime, CAST(DATE_TRUNC('month', o.paid_at) AS date) AS order_month, p.cohort_start, p.cohort_count, o.total_amt FROM tools_shop.orders AS o JOIN profiles AS p ON p.user_id=o.user_id