Денис и аналитика пишет:

SQL Gym Pro #1 - Разбор 💪 Ну что, разбираем решение!

💎****Основное решение Логика простая:

Находим дату первой активности = регистрация Считаем разницу в днях между активностями и регистрацией Проверяем, попадает ли в нужные окна (6-8 дней, 29-31 день)

WITH first_activity AS ( SELECT user_id, MIN(activity_date) as reg_date FROM user_activity WHERE activity_date BETWEEN '2024-01-01' AND '2024-01-31' GROUP BY user_id )

SELECT COUNT(DISTINCT fa.user_id) as total_users,

COUNT(DISTINCT CASE WHEN DATEDIFF(ua.activity_date, fa.reg_date) BETWEEN 6 AND 8 THEN ua.user_id END) as day7_retained,

COUNT(DISTINCT CASE WHEN DATEDIFF(ua.activity_date, fa.reg_date) BETWEEN 29 AND 31 THEN ua.user_id END) as day30_retained,

ROUND(100.0 * COUNT(DISTINCT CASE WHEN DATEDIFF(ua.activity_date, fa.reg_date) BETWEEN 6 AND 8 THEN ua.user_id END) / COUNT(DISTINCT fa.user_id), 2) as ret_7d_pct,

ROUND(100.0 * COUNT(DISTINCT CASE WHEN DATEDIFF(ua.activity_date, fa.reg_date) BETWEEN 29 AND 31 THEN ua.user_id END) / COUNT(DISTINCT fa.user_id), 2) as ret_30d_pct

FROM first_activity fa LEFT JOIN user_activity ua ON fa.user_id = ua.user_id AND ua.activity_date > fa.reg_date

😎 Ключевые моменты:

DISTINCT чтобы юзер не считался дважды
100.0 * чтобы не потерять проценты
LEFT JOIN чтобы учесть всех, даже кто не вернулся

🔥****Усложнение: минимум 2 дня активности WITH first_activity AS ( SELECT user_id, MIN(activity_date) as reg_date FROM user_activity WHERE activity_date BETWEEN '2024-01-01' AND '2024-01-31' GROUP BY user_id ), activity_count AS ( SELECT fa.user_id, COUNT(DISTINCT CASE WHEN DATEDIFF(ua.activity_date, fa.reg_date) BETWEEN 6 AND 8 THEN ua.activity_date END) as days_7, COUNT(DISTINCT CASE WHEN DATEDIFF(ua.activity_date, fa.reg_date) BETWEEN 29 AND 31 THEN ua.activity_date END) as days_30 FROM first_activity fa LEFT JOIN user_activity ua ON fa.user_id = ua.user_id GROUP BY fa.user_id )

SELECT COUNT() as total, SUM(CASE WHEN days_7 >= 2 THEN 1 ELSE 0 END) as ret_7d, SUM(CASE WHEN days_30 >= 2 THEN 1 ELSE 0 END) as ret_30d, ROUND(100.0 * SUM(CASE WHEN days_7 >= 2 THEN 1 ELSE 0 END) / COUNT(), 2) as ret_7d_pct FROM activity_count Тут считаем уникальные дни активности, потом фильтруем тех, у кого >= 2 дней.

Запомнить 📝