LTV

WITH user_profile AS
    (SELECT users.user_id,
           DATE_TRUNC('month', users.created_at)::date AS start_cohort,
           COUNT(*) OVER (PARTITION BY DATE_TRUNC('month', users.created_at)) AS cohort_size
           FROM tools_shop.users AS users)

SELECT
    EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', o.paid_at), up.start_cohort)) AS lifetime,
    DATE_TRUNC('month', o.paid_at)::date AS order_month,
    start_cohort,
    cohort_size,
    total_amt AS revenue

FROM user_profile AS up
JOIN tools_shop.orders AS o ON up.user_id = o.user_id;