Условие

Дана таблица событий fact_table мобильной игры со столбцами user_id, event_name, event_time, currency, amount. Событие event_name = 'install' означает установку игры, event_name = 'payment' — платёж с суммой amount в валюте currency.

Для каждого дня жизни игрока (день 1–10, отсчёт от установки) посчитайте сумму его платежей в следующие 7 и 30 дней. Затем выведите среднее по всем игрокам этой суммы для первых 10 дней жизни.

Результат — 10 строк, 3 колонки: day_of_life, avg_pay_next_7d, avg_pay_next_30d.

Решение

Подход

Базовый трюк — для каждого игрока сгенерировать дни 1..10 от install_date, а затем для каждого такого «якоря» суммировать платежи в окнах [anchor; anchor + 7d) и [anchor; anchor + 30d).

Это можно сделать тремя способами:

generate_series + LATERAL JOIN (PostgreSQL).
CROSS JOIN с днями 1..10 (универсально).
Self-join по диапазону дат (медленно, но читаемо).

Покажу вариант №2 — переносится между диалектами.

Реализация

WITH installs AS (
    SELECT user_id, MIN(event_time::timestamp)::date AS install_date
    FROM fact_table
    WHERE event_name = 'install'
    GROUP BY user_id
),
payments AS (
    SELECT user_id, event_time::timestamp::date AS pay_date, amount
    FROM fact_table
    WHERE event_name = 'payment' AND amount IS NOT NULL
),
day_grid AS (        -- дни жизни 1..10
    SELECT 1 AS day_of_life UNION ALL SELECT 2
    UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6
    UNION ALL SELECT 7 UNION ALL SELECT 8
    UNION ALL SELECT 9 UNION ALL SELECT 10
),
anchors AS (
    SELECT
        i.user_id,
        d.day_of_life,
        i.install_date + (d.day_of_life - 1) * INTERVAL '1 day' AS anchor_date
    FROM installs i
    CROSS JOIN day_grid d
),
per_user_day AS (
    SELECT
        a.user_id,
        a.day_of_life,
        COALESCE(SUM(CASE
            WHEN p.pay_date >= a.anchor_date::date
             AND p.pay_date <  a.anchor_date::date + 7
            THEN p.amount END), 0) AS pay_next_7d,
        COALESCE(SUM(CASE
            WHEN p.pay_date >= a.anchor_date::date
             AND p.pay_date <  a.anchor_date::date + 30
            THEN p.amount END), 0) AS pay_next_30d
    FROM anchors a
    LEFT JOIN payments p
      ON p.user_id = a.user_id
     AND p.pay_date >= a.anchor_date::date
     AND p.pay_date <  a.anchor_date::date + 30
    GROUP BY a.user_id, a.day_of_life
)
SELECT
    day_of_life,
    AVG(pay_next_7d)  AS avg_pay_next_7d,
    AVG(pay_next_30d) AS avg_pay_next_30d
FROM per_user_day
GROUP BY day_of_life
ORDER BY day_of_life;

Анализ результата

Условие LEFT JOIN отбирает диапазон 30 дней — это suffices для обоих окон. CASE внутри сужает до 7d/30d.
COALESCE(..., 0) — игрок без платежей в окне даёт 0, а не NULL. Иначе AVG исключит NULL и среднее будет смещено.
Знаменатель в AVG — все игроки, у которых был install (через anchors).

Альтернатива через окно

В современных Postgres / BigQuery можно использовать RANGE BETWEEN INTERVAL:

SELECT
    user_id, day_of_life,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY pay_date
        RANGE BETWEEN CURRENT ROW AND INTERVAL '7 days' FOLLOWING
    ) AS pay_next_7d
FROM ...

Но эта форма не везде поддерживается, и для «10 фиксированных якорей» проще явный grid.

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

Цензура справа. Если данных мало (например, последний install — вчера), у части игроков 30-дневное окно ещё не закрылось. Среднее по всем будет смещено вниз. Чаще считают только тех, у кого окно полностью закрыто, либо помечают «свежих» отдельно.
Несколько валют. Сумма по сырым amount без конверсии — мусор; в реальной задаче нужна таблица курсов на дату платежа.
Day_of_life vs days_since_install. «День 1» — это день установки или следующий? В нашей реализации день 1 = install_date, день 2 = +1d и так далее. Уточняйте у заказчика.
Окно [anchor; anchor+7d). Полуинтервал — стандарт. BETWEEN ... AND ... включает обе границы, что даст +1 день; используйте >= и <.
Память. На больших данных CROSS JOIN × LEFT JOIN × 10 дней раздувается. На реальных кластерах используйте партиционирование по install_date или RANGE OVER.
NULL в amount. Платежи с NULL надо исключить — они не должны участвовать в сумме.

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

Таблица 10 строк × 3 колонки. Метод: для каждого игрока строим сетку якорей дня 1..10 → суммируем платежи в окнах [anchor; anchor+7d) и [anchor; anchor+30d) → усредняем по игрокам, считая «нулевых». Помним про право-цензуру окон и валюту.

ScoreWarrier: средние платежи в следующие 7/30 дней по дням жизни игрока