Условие

В мобильной игре есть таблицы:

payment(time, user_id, mobile, transaction_id, rev) — платежи. mobile = 1 — мобайл, 0 — ПК.
users(user_id, first_name, age, reg_time) — пользователи.

Задание 1: SQL для расчёта суммы покупок, числа платежей и числа плательщиков по датам, с мобильных устройств игроками, зарегистрированными более 7 дней назад.

Задание 2: построить отчёт (Tableau/Data Studio) с кумулятивными показателями по дням 3, 7, 14, 28 после регистрации:

% платящих,
ARPU,
ARPPU,
Revenue.

Замечание: значения должны накапливаться (если день N меньше дня N-1 — задание выполнено неверно).

Решение

Подход

Задание 1 — простой агрегат с фильтрами

SELECT
  DATE(p.time)                         AS payment_date,
  SUM(p.rev)                           AS total_revenue,
  COUNT(*)                             AS payments_count,
  COUNT(DISTINCT p.user_id)            AS unique_payers
FROM payment p
JOIN users u ON u.user_id = p.user_id
WHERE p.mobile = 1
  AND DATE(p.time) >= DATE(u.reg_time) + INTERVAL '7 days'  -- зарегистрирован более 7 дней назад
GROUP BY DATE(p.time)
ORDER BY DATE(p.time);

Тонкости:

"Более 7 дней назад" — DATE_DIFF(payment_date, reg_date) > 7 (строгое неравенство) или >= 7 (нестрогое)? По смыслу русского "более" — строго больше, т.е. > 7 или >= 8.
Если у пользователя несколько регистраций (быть не должно, но в сырых данных бывает) — MIN(reg_time).

Задание 2 — кумулятивные ARPU/ARPPU по дню после установки

Построим «длинный» датасет для BI: одна строка на (cohort_date, day_after, метрика).

WITH cohort AS (
  SELECT
    user_id,
    DATE(reg_time) AS cohort_date
  FROM users
),
spend AS (
  SELECT
    c.cohort_date,
    c.user_id,
    DATE(p.time) - c.cohort_date AS day_after,
    p.rev
  FROM cohort c
  LEFT JOIN payment p ON p.user_id = c.user_id
                      AND DATE(p.time) >= c.cohort_date
                      AND DATE(p.time) <= c.cohort_date + INTERVAL '28 days'
),
cum_per_user AS (
  SELECT
    cohort_date,
    user_id,
    day_after,
    SUM(rev) OVER (
      PARTITION BY cohort_date, user_id
      ORDER BY day_after
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cum_revenue_user
  FROM spend
),
agg AS (
  SELECT
    cohort_date,
    day_after,
    SUM(cum_revenue_user)                                              AS revenue,
    COUNT(DISTINCT user_id)                                            AS users_total,
    COUNT(DISTINCT CASE WHEN cum_revenue_user > 0 THEN user_id END)    AS payers
  FROM cum_per_user
  WHERE day_after IN (3, 7, 14, 28)
  GROUP BY cohort_date, day_after
)
SELECT
  cohort_date,
  day_after,
  revenue,
  users_total,
  payers,
  payers * 1.0 / users_total                                AS paying_share,
  revenue * 1.0 / users_total                               AS arpu,
  CASE WHEN payers > 0
       THEN revenue * 1.0 / payers
       ELSE 0
  END                                                       AS arppu
FROM agg
ORDER BY cohort_date, day_after;

Что важно

Кумулятивность

Метрики накопительные означают: «деньги, заплаченные ВСЕМИ юзерами когорты ЗА N дней с момента регистрации». Это и есть кумулятивная сумма платежей по (cohort, user, day_after), агрегированная до (cohort, day_after).

Кумулятивный подход даёт строго монотонно неубывающие Revenue, ARPU, paying_share. ARPPU может колебаться (новый плательщик с маленькой первой покупкой снижает среднее), но Revenue и ARPU должны расти.

Нумерация дней

day_after = 0 (день регистрации) считается? Зависит от соглашения:

Если "Day 1" в задаче = день регистрации, фильтруем day_after IN (2, 6, 13, 27).
Если "Day 1" = первый календарный день после регистрации, оставляем IN (3, 7, 14, 28).

Уточняйте у заказчика.

Реализация — pandas (если SQL ограничен)

import pandas as pd
 
users = pd.read_csv("users.csv", parse_dates=["reg_time"])
pmt   = pd.read_csv("payment.csv", parse_dates=["time"])
 
users["cohort_date"] = users["reg_time"].dt.date
m = pmt.merge(users[["user_id", "cohort_date"]], on="user_id")
m["day_after"] = (m["time"].dt.date - m["cohort_date"]).map(lambda x: x.days)
 
# Кумулятивная сумма по юзеру
m = m.sort_values(["user_id", "day_after"])
m["cum_rev_user"] = m.groupby("user_id")["rev"].cumsum()
 
# Аггрегируем
checkpoints = [3, 7, 14, 28]
result = []
for d in checkpoints:
    snap = (m.query(f"day_after <= {d}")
              .groupby("user_id")
              .agg(rev=("rev", "sum")))
    cohort_users = users.groupby("cohort_date")["user_id"].nunique()
    revenue = snap["rev"].sum()
    payers = (snap["rev"] > 0).sum()
    # ... per cohort ...

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

Финальный отчёт даёт классические LTV-кривые:

% платящих — растёт быстрее всего в первые дни.
ARPU — линейно нарастает; его рост за первые 7 дней — leading indicator LTV-30.
ARPPU — может вырасти/упасть/быть нестабильным, потому что когда приходит новый платящий с небольшой покупкой, ARPPU падает.
Revenue — абсолютная кумулятивная сумма; полезно для recency-сравнений.

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

«Накопительность» — главный источник ошибок. Без OVER (... ROWS UNBOUNDED PRECEDING) или предварительного cumsum метрика не накопится.
«Более 7 дней назад» — строгое или нестрогое неравенство? Уточняйте.
mobile = 1 only — обязательный фильтр.
Кумулятивный ARPPU не монотонен — это не баг, это особенность метрики. Только Revenue и (за 28 дней) ARPU монотонны.
Юзеры без платежей. Они должны быть в знаменателе ARPU, но не должны попадать в платежей. LEFT JOIN users → payment с COALESCE(rev, 0).
Часовые пояса. DATE(time) - DATE(reg_time) — корректно только если оба в одном tz. На сервере — UTC.
day_after отрицательный. Если payment до регистрации — bug данных. Фильтр day_after >= 0 обязателен.
Когорты без полных N дней. Если данные собирались по dt < today, последняя когорта (зарегистрировалась вчера) имеет всего 1 день, а не 28. Маркируйте как NULL для незрелых дат.
Незаконченные дни. Если день частичный, ARPU будет занижен. Используйте только полные дни.

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

Задание 1: SELECT ... GROUP BY DATE(time) с фильтрами mobile = 1 и DATE(p.time) > DATE(u.reg_time) + INTERVAL '7 days'.

Задание 2: CTE с кумулятивной суммой платежей per user через окно SUM() OVER (PARTITION BY user_id ORDER BY day_after ROWS UNBOUNDED PRECEDING) → агрегация по (cohort_date, day_after IN (3,7,14,28)) → расчёт paying_share, ARPU, ARPPU, revenue.

Главное — правильная кумулятивность, корректный знаменатель (все юзеры когорты, не только платящие) и осознание, что ARPPU может не быть монотонным.

Zebomba Game — кумулятивные ARPU/ARPPU/Revenue по дням после регистрации