Сегодня у нас SQL с интервью в Revolut ❤️ Мне эту задачку зарепортили уже три человека, так что все для вас - свежак с полей 🌾
Схема БД большая, целых три таблицы, поэтому я упаковала ее в картинку (см комменты). Но сразу оговорюсь про главный подвох!!
🚨 На собесе не было ни картинки, ни красивых стрелочек. Все условие дается сплошным текстом, а связи между таблицами приходится вылавливать из описаний полей самому.
Условие:
Найти пользователей, у которых суммарный объем транзакций по продукту CRYPTO составил не меньше £100 за первые 7 дней с момента регистрации.
По классике - внимательно читаем условие и вылавливаем связки. Нам надо понять, что от нас хотят и через какие таблицы это тащить.
Мы хотим найти:
• Инфу по продукту CRYPTO
• Инфу по сумме и дате транзакций
• Инфу по дате регистрации пользователя
➡️ Связка 1: Где вообще живет CRYPTO и сам факт покупки?
users знает только кто и когда зарегался. Факт "пользователь что-то сделал с продуктом CRYPTO" лежит в activity (там есть product и type). А покупка это type = 'CONVERSION' (см. описание).
То есть маршрут начинается так: users → activity (фильтруем product = 'CRYPTO' и type = 'CONVERSION').
➡️ Связка 2: Где сумма транзакции?
В activity суммы нет. Но у строки с CONVERSION есть transaction_id, который ведет в transactions, а вот там уже amount_gbp. Значит, тянем дальше: activity → transactions по transaction_id.
Тут, кстати, надо спросить у проверяющего, какие транзакции нас интересуют:
• IN/OUT?
• COMPLETED/DECLINED?
Базовый ответ с собеседования - интересуют и IN, и OUT, но нужно фильтровать по COMPLETED.
➡️ Связка 3: "за первые 7 дней с регистрации"
Первые 7 дней отсчитываются от users.created_date (дата регистрации), а сама транзакция это transactions.created_date. Мы так и вели это от таблички users к табличке transactions, так что ничего не потеряли.
🔥 Решение
SELECT
a.user_id,
SUM(CASE WHEN c.created_date >= a.created_date
AND c.created_date::date <= a.created_date::date + 7
THEN c.amount_gbp ELSE 0 END) AS amount_first_7_days
FROM users a
JOIN activity b
ON a.user_id = b.user_id
AND b.type = 'CONVERSION'
AND b.product = 'CRYPTO'
JOIN transactions c
ON b.transaction_id = c.transaction_id
AND c.state = 'COMPLETED'
GROUP BY a.user_id
HAVING SUM(CASE WHEN c.created_date >= a.created_date
AND c.created_date::date <= a.created_date::date + 7
THEN c.amount_gbp ELSE 0 END) >= 100;
1️⃣ Почему везде JOIN, а не LEFT JOIN?
Нас интересуют ТОЛЬКО пользователи, реально совершившие крипто-покупку на £100+. Если у пользователя нет ни одной CONVERSION-активности по CRYPTO - он нам не нужен. Если у активности нет успешной транзакции - она тоже не должна попадать в сумму.
2️⃣ Почему фильтры в ON, а не в WHERE?
Мне просто нравится держать фильтр таблицы рядом с ее присоединением - так сразу видно, на каком шаге и что мы отсекаем. Но если вам привычнее собрать все в WHERE, то на собесе вряд ли будут придираться ❤️
3️⃣ Пара мелочей касательно дат
• ::date - это приведение к дате (обрезаем время). Нужно, чтобы 7 дней считались по календарным дням, а не по точным timestamp с часами и минутами. Иначе транзакция на 7-й день, но парой часов позже момента регистрации, могла бы не попасть.
• Всегда проговаривайте с собеседующим, "первые 7 дней" - это включительно или нет, иначе есть риск ошибиться на единицу
————
Ну шо, колитесь, смогли бы сориентироваться, если бы не было схемы? 🤑
По стандарту - жду ваших 🔥🔥🔥
Это греет мне душу, когда я пишу новые разборы 😘
Предыдущие выпуски:
vol. 1 | vol. 2 | vol. 3 | vol. 4 | vol. 5
vol. 6 | vol. 7 | vol. 8 | vol. 9 | vol. 10