StrataScratch (Microsoft) — power users: >10 покупок за последний месяц

Условие

Таблицы:

• ms_user_dimension(user_id, acc_id)
• ms_acc_dimension(acc_id, paying_customer) — Y/N
• ms_purchase_dim(user_id, paid) — лог покупок (без даты в учебной версии; для условия добавим колонку purchase_date).

Найдите всех платных (paying_customer = 'Y') пользователей, у которых в последние 30 дней > 10 покупок.

Решение

SELECT u.user_id, COUNT(*) AS purchases_30d
FROM ms_purchase_dim p
JOIN ms_user_dimension u ON u.user_id = p.user_id
JOIN ms_acc_dimension a  ON a.acc_id  = u.acc_id
WHERE a.paying_customer = 'Y'
  AND p.purchase_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY u.user_id
HAVING COUNT(*) > 10;

CURRENT_DATE - INTERVAL '30 days' vs hard-coded

Лучше параметризовать через переменную / параметр приложения. В чистом SQL CURRENT_DATE - 30 (Postgres) или DATE_SUB(CURDATE(), INTERVAL 30 DAY) (MySQL).

Куда сунуть фильтр по paying_customer

В WHERE — отфильтруется до JOIN/группировки, что эффективнее. Альтернатива: фильтр в HAVING через MAX(paying_customer) — медленнее.

Подводные камни

1. paying_customer = 'Y' case-sensitive. Если значения в данных смешаны (y/Y/yes) — UPPER(paying_customer) = 'Y' или нормализация на ETL.
2. > 10 vs >= 10. «More than 10» — строго > 10 (то есть 11+). Перепроверяйте.
3. Часовая зона CURRENT_DATE. В Postgres CURRENT_DATE — это локаль сессии. Если данные в UTC, а аналитик в MSK — расхождение в 3 часа на границе суток.
4. Дубликаты acc_id per user. Один user может иметь несколько acc — JOIN задвоит покупки. DISTINCT по user_id в финальном или COUNT(DISTINCT purchase_id) защищают.

Эталонный ответ

JOIN трёх таблиц + WHERE paying_customer='Y' AND purchase_date >= now-30 + GROUP BY user_id HAVING COUNT > 10. Самый классический BI-запрос.