SQL Gym Pro #2 — разбор задачи

Привет, друзья! В этой задаче мы считаем конверсию из установки приложения в покупку. Разбираем 3 подхода:

✅ Задача: Нужно было посчитать:

1. Сколько пользователей установили приложение

2. Сколько из них совершили покупку

3. Конверсию между этими шагами

Решение #1: Самый простой способ SELECT COUNT(DISTINCT user_id) AS total_installs, COUNT(DISTINCT CASE WHEN action = 'purchase' THEN user_id END) AS users_purchased, ROUND(100.0 * COUNT(DISTINCT CASE WHEN action = 'purchase' THEN user_id END) / COUNT(DISTINCT user_id), 2) AS conversion_rate FROM user_actions WHERE date BETWEEN '2024-02-01' AND '2024-02-29';

🔑 Важно:

COUNT(DISTINCT user_id) — считаем уникальных пользователей (установки)

CASE WHEN action = 'purchase' — выделяем тех, кто купил

ROUND(..., 2) — округляем до двух знаков

Результат: 📊 Установок: 4 🛒 Покупок: 2 🔑 Конверсия: 50% Решение #2: Более структурированный подход через CTE WITH installs AS ( SELECT DISTINCT user_id FROM user_actions WHERE action = 'install' AND date BETWEEN '2024-02-01' AND '2024-02-29' ), purchases AS ( SELECT DISTINCT user_id FROM user_actions WHERE action = 'purchase' AND date BETWEEN '2024-02-01' AND '2024-02-29' ) SELECT COUNT(DISTINCT i.user_id) AS total_installs, COUNT(DISTINCT p.user_id) AS users_purchased, ROUND(100.0 * COUNT(DISTINCT p.user_id) / COUNT(DISTINCT i.user_id), 2) AS conversion_rate FROM installs i LEFT JOIN purchases p ON i.user_id = p.user_id;

🎯 Преимущества:

1. Читаемость

2. Легкость масштабирования (можно добавлять шаги воронки)

3. Анализ пересечения множества

Решение #3: Для цепочки install → registration → purchase SELECT COUNT(DISTINCT user_id) AS total_installs, COUNT(DISTINCT CASE WHEN action = 'registration' THEN user_id END) AS users_registered, COUNT(DISTINCT CASE WHEN action = 'purchase' THEN user_id END) AS users_purchased, ROUND(100.0 * COUNT(DISTINCT CASE WHEN action = 'registration' THEN user_id END) / COUNT(DISTINCT user_id), 2) AS install_to_reg_conv, ROUND(100.0 * COUNT(DISTINCT CASE WHEN action = 'purchase' THEN user_id END) / NULLIF(COUNT(DISTINCT CASE WHEN action = 'registration' THEN user_id END), 0), 2) AS reg_to_purchase_conv, ROUND(100.0 * COUNT(DISTINCT CASE WHEN action = 'purchase' THEN user_id END) / COUNT(DISTINCT user_id), 2) AS install_to_purchase_conv FROM user_actions WHERE date BETWEEN '2024-02-01' AND '2024-02-29';

⚠️ Важно: Используем NULLIF чтобы избежать деления на ноль, если нет регистраций.

🎓 Выводы:

Конверсия = (Пользователи на текущем шаге / Пользователи на предыдущем шаге) × 100%

Всегда используйте DISTINCT при подсчете пользователей

Учитывайте деление на ноль

📈 Ручной расчет:

Установили: 101, 102, 103, 104 = 4 пользователя

Купили: 101, 103 = 2 пользователя

Конверсия: 2/4 × 100% = 50%

Остались вопросы? Давайте вместе разбираться!