SQL Gym Pro #3 - Разбор задачи LTV (Lifetime Value) — это сколько денег приносит один пользователь за всё время жизни в продукте.

Зачем считать: 🔹Понимать, окупается ли привлечение пользователей 🔹Сравнивать эффективность каналов привлечения 🔹Прогнозировать доходы

✅ Решение базовой задачи Шаг 1: Находим когорту пользователей WITH cohort_users AS ( SELECT user_id, DATE_TRUNC('month', reg_date) as cohort_month, country FROM users WHERE reg_date BETWEEN '2024-01-01' AND '2024-01-31' )

Что делаем: выбираем только январских пользователей 2024 года. DATE_TRUNC округляет дату до месяца, чтобы группировать по когортам.

Шаг 2: Считаем LTV для каждого пользователя user_ltv AS ( SELECT cu.user_id, cu.cohort_month, cu.country, COALESCE(SUM(p.amount), 0) as ltv_90d, COUNT(p.purchase_id) as purchases_count FROM cohort_users cu LEFT JOIN purchases p ON cu.user_id = p.user_id AND DATEDIFF(p.purchase_date, cu.reg_date) BETWEEN 0 AND 90 GROUP BY cu.user_id, cu.cohort_month, cu.country )

Шаг 3: Агрегируем по странам SELECT cohort_month, country, COUNT(user_id) as users_count, SUM(ltv_90d) as total_ltv, SUM(purchases_count) as total_purchases, ROUND(SUM(ltv_90d) / NULLIF(SUM(purchases_count), 0), 2) as avg_check, ROUND(SUM(ltv_90d) / COUNT(user_id), 2) as arpu FROM user_ltv GROUP BY cohort_month, country ORDER BY cohort_month, country;

🚀 Решение с усложнением (разбивка по месяцам) WITH cohort_users AS ( SELECT user_id, reg_date, country FROM users WHERE reg_date BETWEEN '2024-01-01' AND '2024-01-31' ) SELECT cu.user_id, cu.country, -- Месяц 1: дни 0-30 COALESCE(SUM(CASE WHEN DATEDIFF(p.purchase_date, cu.reg_date) BETWEEN 0 AND 30 THEN p.amount END), 0) as ltv_month1, -- Месяц 2: дни 31-60 COALESCE(SUM(CASE WHEN DATEDIFF(p.purchase_date, cu.reg_date) BETWEEN 31 AND 60 THEN p.amount END), 0) as ltv_month2, -- Месяц 3: дни 61-90 COALESCE(SUM(CASE WHEN DATEDIFF(p.purchase_date, cu.reg_date) BETWEEN 61 AND 90 THEN p.amount END), 0) as ltv_month3, -- Общий LTV за 90 дней COALESCE(SUM(CASE WHEN DATEDIFF(p.purchase_date, cu.reg_date) BETWEEN 0 AND 90 THEN p.amount END), 0) as ltv_90d FROM cohort_users cu LEFT JOIN purchases p ON cu.user_id = p.user_id AND DATEDIFF(p.purchase_date, cu.reg_date) BETWEEN 0 AND 90 GROUP BY cu.user_id, cu.country;

Тренировка окончена) Отлично поработали! Сегодня мы прокачали финансовые мышцы.

А какой параметр LTV важнее для вашего бизнеса прямо сейчас?

1. Высокий средний чек (пользователи платят много, но редко)

2. Высокая частота покупок (платят мало, но часто)

3. Долгий lifetime (платят стабильно много месяцев)

Голосуйте цифрой в комментариях!