Составляем профили
- идентификатор пользователя
- начальный момент времени (время появления пользователя)
SELECT p.user_id,
DATE_TRUNC('day', first_ts)::date AS start_cohort,
COUNT(*) OVER (PARTITION BY DATE_TRUNC('day', first_ts)) AS cohort_size
FROM online_store.profiles p;
Собираем таблицу сессий
WITH user_profile AS (SELECT p.user_id,
DATE_TRUNC('day', first_ts)::date AS start_cohort,
COUNT(*) OVER (PARTITION BY DATE_TRUNC('day', first_ts)) AS cohort_size
FROM online_store.profiles p)
SELECT EXTRACT(DAY FROM AGE(DATE_TRUNC('day', o.event_dt), up.start_cohort)) AS lifetime,
o.event_dt AS order_date,
start_cohort,
cohort_size,
revenue
FROM user_profile up
JOIN online_store.orders o ON up.user_id = o.user_id;
-- в результате остаются только записи тех user_id, которые совершили покупки (размер когорты уже посчитан)
Считаем LTV
WITH user_profile AS (SELECT p.user_id,
DATE_TRUNC('day', first_ts)::date AS start_cohort,
COUNT(*) OVER (PARTITION BY DATE_TRUNC('day', first_ts)) AS cohort_size
FROM online_store.profiles p),
sessions AS (SELECT EXTRACT(DAY FROM AGE(DATE_TRUNC('day', o.event_dt), up.start_cohort)) AS lifetime,
o.event_dt AS order_date,
start_cohort,
cohort_size,
revenue
FROM user_profile up
JOIN online_store.orders o ON up.user_id = o.user_id)
SELECT lifetime,
start_cohort,
SUM(revenue) OVER (PARTITION BY start_cohort ORDER BY lifetime) / cohort_size AS ltv
FROM sessions;
--запрос порождает большое количество дублей т.к. мы избавились от order_date
WITH user_profile AS (SELECT p.user_id,
DATE_TRUNC('day', first_ts)::date AS start_cohort,
COUNT(*) OVER (PARTITION BY DATE_TRUNC('day', first_ts)) AS cohort_size
FROM online_store.profiles p),
sessions AS (SELECT EXTRACT(DAY FROM AGE(DATE_TRUNC('day', o.event_dt), up.start_cohort)) AS lifetime,
o.event_dt AS order_date,
start_cohort,
cohort_size,
revenue
FROM user_profile up
JOIN online_store.orders o ON up.user_id = o.user_id),
ltv_raw AS (SELECT lifetime,
start_cohort,
SUM(revenue) OVER (PARTITION BY start_cohort ORDER BY lifetime) / cohort_size AS ltv
FROM sessions)
SELECT lifetime,
start_cohort,
ltv
FROM ltv_raw
GROUP BY lifetime, start_cohort, ltv
ORDER BY start_cohort, lifetime;
Другой пример:
WITH user_profile AS (
SELECT u.user_id,
CAST(DATE_TRUNC('month', u.created_at) AS date) as start_cohort,
COUNT(u.user_id) OVER(PARTITION BY CAST(DATE_TRUNC('month', u.created_at) AS date)) AS cohort_size
FROM tools_shop.users AS u
),
sessions AS (
SELECT EXTRACT(MONTH FROM AGE(
DATE_TRUNC('day', o.created_at),
p.start_cohort
)) AS lifetime,
CAST( DATE_TRUNC('month', o.created_at) AS date) order_date, --FUCK
p.start_cohort,
p.cohort_size,
o.total_amt
FROM user_profile AS p
JOIN tools_shop.orders AS o ON p.user_id = o.user_id
),
ltv_raw AS (
SELECT lifetime,
start_cohort,
cohort_size,
SUM(total_amt) OVER (PARTITION BY start_cohort ORDER BY lifetime) / cohort_size AS ltv
FROM sessions
)
SELECT lifetime,
start_cohort,
ltv
FROM ltv_raw
WHERE EXTRACT(YEAR FROM start_cohort) = 2019
GROUP BY lifetime, start_cohort, ltv
ORDER BY start_cohort, lifetime;
было бы неплохо ещё знать, что раньше-то было, но тут не показывается 
но тогда пропадут все изменения, включая уже применённые. 
