ltv3

  WITH user_profile AS
    (SELECT u.user_id,
           EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', o.created_at), DATE_TRUNC('month', u.created_at)::date)) AS lifetime,
           DATE_TRUNC('month', o.created_at)::date AS order_month,
           DATE_TRUNC('month', u.created_at)::date AS start_cohort,
           COUNT(*) OVER (PARTITION BY DATE_TRUNC('month', u.created_at)) AS cohort_size,
           total_amt AS revenue
           FROM tools_shop.users AS u
           JOIN tools_shop.orders AS o ON u.user_id = o.user_id)

SELECT
    lifetime,
    start_cohort,
    cohort_size,
    SUM(revenue) OVER (PARTITION BY start_cohort ORDER BY lifetime) / cohort_size AS ltv

FROM user_profile AS up