У вас есть две таблицы:
🔵логирование всех входов игрока в игру: в колонке
created дата и время первого входа в игру; в колонке user_id идентификатор игрока,🔵таблица всех платежей игроков: в колонке
created дата и время платежа; в колонке user_id идентификатор игрока.Нужно написать код на SQL, который построит таблицу с расчетом LTV для недельной когорты новых игроков.
Что проверяет такая задача?
🔵базовое знание SQL
🔵умение составить расчет заданной метрики
Как можно решить?
1. Вспомним, что такое LTV: метрика Lifetime Value демонстрирует, сколько прибыли компания получила от клиента за заданное время. Она показывает эффективность маркетинга и компании в целом. Cчитается так: общий доход за период разделить на количество покупателей за этот же период.
2. Посчитаем суммарную выручку на N-й день после входа в игру для каждой когорты юзеров. Когорта — неделя первого входа юзера в игру. Обычно начальной точкой считается дата установки игры, но в задании она не дана.
3. Посчитаем для каждой когорты число юзеров в ней (без привязки к дню).
4. Поcчитаем кумулятивную выручку каждой когорты в динамике по дням.
5. Результаты разделим на количество юзеров в когорте — получатся значения LTV.
➡️ Код такого решения будет выглядеть так:
first_login as (
select user_id, min(created) as first_log from login_info group by 1
),
cohorts as (
select date_trunc('week', first_log)::date as first_log_week,
count(distinct user_id) as users
from first_login
group by 1
)
select distinct
c.first_log_week as Week_start,
pi.created - fl.first_log as Day_after,
sum(pi.sum_rub) over(partition by c.first_log_week order by pi.created) / users as LTV
from first_login fl
left join payment_info pi using(user_id)
left join cohorts c on c.first_log_week=date_trunc('week', fl.first_log)::date
order by 1,2
Задавайте вопросы и предлагайте ваши решения в комментариях!
@leftjoin_career