Условие

Есть две таблицы:

logins(created, user_id) — все входы игроков в игру.
payments(created, user_id, sum_rub) — все платежи.

Постройте SQL, который вернёт таблицу LTV для недельной когорты новых игроков:

Week_start	Day_after	LTV
2021-08-30	1	10
2021-08-30	2	20
2021-08-30	3	27
...	...	...
2021-09-06	1	12

Где:

Week_start — дата начала недели когорты (понедельник недели первого входа игрока).
Day_after — день после первого входа (1, 2, 3, ...).
LTV — кумулятивный средний доход на пользователя когорты к этому дню.

Решение

Подход

LTV(t) = (сумма выручки от когорты за первые t дней) / (число игроков в когорте). Отсюда план:

Когорта пользователя — неделя его первого входа (используем MIN(created) по user_id).
Размер когорты = COUNT(DISTINCT user_id) для week_start.
Платежи каждого юзера — относительно его собственного первого входа: day_after = (payment_date - first_login_date).
Кумулятивная сумма платежей по (week_start, day_after).
Деление на размер когорты.

Реализация (PostgreSQL / BigQuery-совместимо)

WITH first_login AS (
  SELECT
    user_id,
    DATE(MIN(created))                                   AS first_login_date,
    DATE_TRUNC('week', MIN(created))::date               AS week_start
  FROM logins
  GROUP BY user_id
),
cohort_size AS (
  SELECT
    week_start,
    COUNT(DISTINCT user_id) AS users_in_cohort
  FROM first_login
  GROUP BY week_start
),
revenue_by_day AS (
  SELECT
    fl.week_start,
    DATE(p.created) - fl.first_login_date                AS day_after,
    SUM(p.sum_rub)                                       AS revenue_day
  FROM first_login fl
  JOIN payments p USING (user_id)
  WHERE DATE(p.created) >= fl.first_login_date
    AND DATE(p.created) <  fl.first_login_date + INTERVAL '30 days'
  GROUP BY fl.week_start, day_after
),
cumulative AS (
  SELECT
    week_start,
    day_after,
    SUM(revenue_day) OVER (
      PARTITION BY week_start
      ORDER BY day_after
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cum_revenue
  FROM revenue_by_day
)
SELECT
  c.week_start,
  c.day_after,
  ROUND(c.cum_revenue / cs.users_in_cohort, 2) AS ltv
FROM cumulative c
JOIN cohort_size cs USING (week_start)
WHERE c.day_after BETWEEN 1 AND 30
ORDER BY c.week_start, c.day_after;

Что важно учесть

1. Граница «нулевого дня»

В примере вывод начинается с Day_after = 1. Что считать «днём 1»?

Вариант A: day 1 = day_of_first_login (платёж в момент входа считается).
Вариант B: day 1 = day_after_first_login (платежи следующего дня).

Уточняйте у заказчика. В коде выше я фильтровал day_after BETWEEN 1 AND 30, но если день первого входа имеет day_after = 0, нужно это учитывать.

2. Кумулятивность через окно

SUM(revenue_day) OVER (... ORDER BY day_after ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — это canonical способ кумулятивной суммы. Без UNBOUNDED PRECEDING окно по умолчанию начинается с текущей строки → не накопится.

3. Дни без платежей

В revenue_by_day нет строк за дни, где не было платежей. Кумулятивная LTV будет «прыгать»: например, day_after = 1 → 5, day_after = 2 → 5 (неизменно), day_after = 3 → 8. На самом деле для дня 2 строки нет — она пропустится, и в выводе будут «дыры».

Если нужны все дни (включая «пустые») — генерируйте календарь:

WITH calendar AS (
  SELECT week_start, generate_series(1, 30) AS day_after
  FROM cohort_size
),
...
revenue_by_day_filled AS (
  SELECT cal.week_start, cal.day_after,
         COALESCE(rev.revenue_day, 0) AS revenue_day
  FROM calendar cal
  LEFT JOIN revenue_by_day rev USING (week_start, day_after)
)

4. SQLite-совместимость

В SQLite нет DATE_TRUNC. Используйте:

DATE(MIN(created), 'weekday 0', '-6 days') -- понедельник

или

DATE(MIN(created), '-' || (CAST(strftime('%w', MIN(created)) AS INTEGER) + 6) % 7 || ' days')

(зависит от того, какой день считать началом недели).

Анализ / интерпретация

Эта таблица — основа для классических визуализаций:

Кривые LTV по когортам — сравнить, растёт ли LTV с новыми когортами (UA-качество).
Время до плато — на каком дне LTV перестаёт расти существенно.
«Magic-number» для бизнеса: LTV30 vs CPI — окупается ли реклама.

Подводные камни

MIN(created) per user. Не created глобально, не MIN по дате — именно по timestamp первого входа.
week_start для разных юзеров. У одного юзера он один — не зависит от других. Но в когорте все юзеры одной недели.
first_login vs created_account. Если в logins фиксируется только запуск игры, а у юзеров есть «зарегистрировался, но не зашёл» — LTV будет искажена.
Платежи до первого входа. Иногда баг данных или re-install. Условие DATE(p.created) >= fl.first_login_date — обязательно.
Платежи по чужим юзерам. JOIN ... USING(user_id) корректен; не используйте cross-join.
Деление на ноль: users_in_cohort = 0 — не должно быть, но на всякий — NULLIF(users_in_cohort, 0).
Refunds. Если в payments есть отрицательные sum_rub (возвраты) — учтите; LTV должна быть net.
«Кумулятивный» — это с дня 1. Не с произвольного дня.
Незрелые когорты. Самая поздняя когорта имеет всего N полных дней — её LTV(30) недопредставлен. Маркируйте такие как NULL или фильтруйте.

Эталонный ответ

Структура SQL:

CTE first_login — MIN(created) по user_id, week_start = DATE_TRUNC('week', _).
CTE cohort_size — COUNT(DISTINCT user_id) по week_start.
CTE revenue_by_day — JOIN payments, считаем day_after = payment_date - first_login_date, SUM(sum_rub) по (week_start, day_after).
CTE cumulative — кумулятивная сумма с SUM() OVER (PARTITION BY week_start ORDER BY day_after ROWS UNBOUNDED PRECEDING).
Финал — деление кумулятивной суммы на размер когорты.

My Games — SQL: расчёт LTV для недельной когорты новичков