ScoreWarrier: SQL — сумма платежей 2–7, средние логины и платежи в первые 4 недели

Условие

Дана таблица fact_table с событиями игроков мобильной игры:

Игрок считается когортным, только если у него есть install (иначе считать когортные метрики нельзя).

Напишите оптимальные SQL-запросы для:

1. Заплаченную сумму со 2-го по 7-й (включительно) платёж на игрока по всем данным. Результат: одно число.
2. Среднее количество логинов и платежей в первые 4 недели с момента установки игры. Результат: 2 числа.

Решение

Подход

Фундамент обоих запросов — ROW_NUMBER() для упорядочивания событий внутри игрока и JOIN к install-времени для отсечки «4 недели от установки».

Полагаем, что event_time — строка вида 'YYYY-MM-DD HH:MM:SS'; кастуем в TIMESTAMP.

Реализация

-- Q1: сумма платежей 2..7 на игрока, агрегированная по всем
WITH payments_ranked AS (
    SELECT
        user_id,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY event_time::timestamp
        ) AS payment_idx
    FROM fact_table
    WHERE event_name = 'payment'
      AND amount IS NOT NULL
)
SELECT SUM(amount) AS sum_payments_2_to_7
FROM payments_ranked
WHERE payment_idx BETWEEN 2 AND 7;

-- Q2: среднее число логинов и платежей в первые 28 дней с момента install
WITH installs AS (
    SELECT
        user_id,
        MIN(event_time::timestamp) AS install_ts
    FROM fact_table
    WHERE event_name = 'install'
    GROUP BY user_id
),
events_4w AS (
    SELECT
        i.user_id,
        f.event_name
    FROM installs i
    JOIN fact_table f
      ON f.user_id = i.user_id
     AND f.event_time::timestamp >= i.install_ts
     AND f.event_time::timestamp <  i.install_ts + INTERVAL '28 days'
    WHERE f.event_name IN ('login', 'payment')
),
per_user AS (
    SELECT
        user_id,
        SUM(CASE WHEN event_name = 'login'   THEN 1 ELSE 0 END) AS logins_4w,
        SUM(CASE WHEN event_name = 'payment' THEN 1 ELSE 0 END) AS payments_4w
    FROM events_4w
    GROUP BY user_id
)
SELECT
    AVG(logins_4w)   AS avg_logins_4w,
    AVG(payments_4w) AS avg_payments_4w
FROM per_user;

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

• В Q1 нет GROUP BY user_id, потому что просили общую сумму. Если у игрока меньше двух платежей — он не попадает (диапазон 2..7 пуст).
• В Q2 per_user гарантирует, что игроки без событий учитываются как 0 (потому что они есть в installs). Это важно: иначе среднее будет завышено.
• Если нужно среднее только по активным, заменить installs на тех, кто что-то делал в окне.

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

1. event_time — TEXT. Лексикографическая сортировка работает только для формата YYYY-MM-DD HH:MM:SS. Лучше явно кастовать в timestamp — иначе ловите баги на разных форматах.
2. Игроки без install. Условие задачи: их не считаем; в Q2 они отсеются естественно через JOIN.
3. Дубли событий. Если (user_id, event_time, event_name) могут дублироваться, добавляйте DISTINCT или MIN. На практике в gameanalytics такое бывает (двойная запись через retry SDK).
4. Среднее без нулей. Если делать AVG сразу из events_4w после GROUP BY user_id, в выборку не попадут игроки без логинов в окне → знаменатель занижен → среднее завышено. Используйте LEFT JOIN от installs.
5. Множественные валюты. Сумма по разным валютам без курса — мусор. Если есть USD, EUR, RUB — нужна конверсия по курсу события.
6. BETWEEN 2 AND 7 включает 7. Это и просили, но прочитайте задание дважды — частая ошибка.

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

Q1 — одно число: сумма по ROW_NUMBER() 2..7 от платежей каждого игрока. Q2 — два числа: среднее логинов и среднее платежей за 28 дней с install. Среднее берётся по всем игрокам с install (включая «нулевых»).