Читать книгу SQL для аналитика. 50 задач с решениями на PostgreSQL - Ар'лан ис'Дрекхэм - Страница 12
Часть 2. Продуктовая аналитика
Глава 10. GROUP BY, SUM. LTV пользователя
ОглавлениеОписание задачи
LTV (Lifetime Value) – общая сумма денег, потраченная пользователем в магазине за всё время. Это ключевая метрика для оценки рейтинга клиента.
Всегда нужно знать: кто приносит больше всего денег, чтобы фокусироваться на удержании ценных клиентов, предложить скидки, подарить купон на день рождения.
Основные SQL-конструкции
SUM – агрегатная функция, суммирует значения в группе. Работает только для числовых колонок.
text
SUM (числовое_поле)
GROUP BY – группирует строки с одинаковыми значениями (разобрано в главе 4).
COALESCE – возвращает первое не-NULL значение. Полезно для замены NULL на 0.
text
COALESCE (column, 0) – если column NULL, вернёт 0
Совет
При суммировании лучше использовать SUM (COALESCE (amount, 0)), чтобы избежать NULL в результате. Но в нашей схеме суммы не NULL.
SQL-запросы с объяснением
Задача 1. LTV каждого пользователя (все заказы).
Посчитаем, сколько всего денег потратил каждый пользователь по завершённым заказам.
text
SELECT
u.user_id,
u.user_name,
SUM (oi. quantity * oi.price_per_unit) AS ltv
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o. order_id = oi. order_id
WHERE o.status = ’completed’
GROUP BY u.user_id, u.user_name
ORDER BY ltv DESC;
Как это работает
– Присоединяем заказы и товары в заказах.
– Фильтруем только завершённые заказы.
– Группируем по пользователю.
– Суммируем стоимость всех купленных товаров.
– Сортируем от самого ценного клиента к менее ценным.
Пример вывода (фрагмент):
text
user_id user_name ltv
1 alex_ivanov 543000
5 nurlan_kz 324000
2 li_wei 149800
3 aram_sargsyan 120000
Задача 2. LTV с учётом только успешных платежей (через payments).
Иногда удобнее считать LTV по таблице платежей, а не по order_items (например, если есть возвраты).
text
SELECT
u.user_id,
u.user_name,
SUM(p.amount) AS ltv
FROM users u
JOIN payments p ON u.user_id = p.user_id
GROUP BY u.user_id, u.user_name
ORDER BY ltv DESC;
Как это работает
– Присоединяем платежи напрямую к пользователям.
– Суммируем суммы платежей.
– Группируем по пользователю.
Типичная ошибка
Забыть GROUP BY при использовании SUM. Без группировки SUM сложит все значения по всей таблице, а не по пользователям.
Задача 3. Средний LTV по странам.
Узнаем, в какой стране пользователи в среднем тратят больше.
text
SELECT
u.country,
COUNT (DISTINCT u.user_id) AS user_count,
SUM (oi. quantity * oi.price_per_unit) AS total_revenue,
ROUND (AVG (oi. quantity * oi.price_per_unit), 2) AS avg_ltv
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o. order_id = oi. order_id
WHERE o.status = ’completed’
GROUP BY u.country
ORDER BY avg_ltv DESC;
Как это работает
– Группируем по стране.
– Считаем количество уникальных пользователей в стране.
– Общую выручку по стране.
– AVG считает средний LTV по стране.
Пример вывода (фрагмент):
text
country user_count total_revenue avg_ltv
Армения 2 240000 120000.00
Россия 5 500000 100000.00
Казахстан 2 150000 75000.00
Египет 1 50000 50000.00
Вопросы и ответы
В: Чем LTV отличается от общей выручки?
О: LTV – сумма на одного пользователя. Общая выручка – сумма по всем пользователям.
В: Как считать LTV для пользователей без заказов?
О: Они не попадут в результат при INNER JOIN. Если нужно показать и их (с LTV = 0), используйте LEFT JOIN и COALESCE.
В: Можно ли считать LTV за определённый период?
О: Да, добавьте фильтр по дате в WHERE, например o. order_date> = «2024-01-01».
В: Почему в задаче 3 используется AVG, а не SUM?
О: AVG даёт средний LTV по стране, SUM – общую выручку. В задаче нужен именно средний.
Что в итоге
Мы научились считать LTV:
– Суммировать стоимость покупок с SUM
– Группировать данные по пользователям и странам
– Считать LTV – ключевую метрику для анализа ценности клиента
* Задание со звёздочкой
Напишите запрос, который выводит топ-3 пользователей по LTV за 2024 год (используйте фильтр по order_date). Выведите user_id, user_name и ltv.
Что в итоге
Мы научились считать LTV:
– Суммировать стоимость покупок с SUM
– Группировать данные по пользователям и странам
– Считать LTV – ключевую метрику для анализа ценности клиента
