Untitled

WITH profile AS
    (SELECT u.user_id,
           DATE_TRUNC('month', created_at)::date AS start_cohort,
           COUNT(*) OVER (PARTITION BY DATE_TRUNC('month', created_at)) AS cohort_size
    FROM tools_shop.users u
     ),
sessions AS
    (SELECT EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', o.created_at)::date, p.start_cohort)) AS lifetime,
            DATE_TRUNC('month', o.created_at)::date AS order_month,
            start_cohort,
            cohort_size,
            o.total_amt
    FROM profile p
    JOIN tools_shop.orders o ON p.user_id = o.user_id
     ),
ltv_raw AS
    (SELECT lifetime,
           start_cohort,
           SUM(total_amt) OVER (PARTITION BY start_cohort ORDER BY lifetime) / cohort_size AS ltv
    FROM sessions
     )
SELECT lifetime,
       start_cohort,
       cohort_size,
       ltv
FROM ltv_raw