SQL и Анализ данных: “🖥 SQL: ЛОВУШКА ДУБЛЕЙ И ДАТ Иногда самая коварная задача

🖥 SQL: ЛОВУШКА ДУБЛЕЙ И ДАТ

Иногда самая коварная задача - найти “покупали 3 дня подряд”. Профи часто делают self-join на даты, но он ломается на дубликатах, таймзонах и нескольких покупках в день.

Подвох: нужно считать не покупки, а уникальные дни, и правильно собрать последовательности без дыр. Решение выглядит просто, но без оконных функций легко ошибиться.

-- Задача:
-- Найти пользователей, которые совершали покупку 3 календарных дня подряд (минимум).
-- Условия:
-- 1) В один день может быть много покупок - считаем день один раз.
-- 2) Нужны именно подряд идущие дни без пропусков.
-- 3) Вернуть user_id и начало/конец самой длинной серии.


WITH days AS (
SELECT DISTINCT
user_id,
DATE(created_at) AS d
FROM orders
),
grp AS (
SELECT
user_id,
d,
-- трюк: для подряд идущих дат разность (дата - номер) постоянна
DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d), d) AS g
FROM days
),
streaks AS (
SELECT
user_id,
MIN(d) AS streak_start,
MAX(d) AS streak_end,
COUNT(*) AS streak_len
FROM grp
GROUP BY user_id, g
)
SELECT user_id, streak_start, streak_end, streak_len
FROM streaks
WHERE streak_len >= 3
ORDER BY streak_len DESC;