Разбор тестового задания на SQL для джуна аналитика данных в маркетинговую компанию
SQL задачи – база любого собеседования для специалиста, работающего с данными. И такие задачи также дают в формате тестового задания.
У вас есть SQL-база с таблицами:
Users(userId, age);Purchases(purchaseId, userId, itemId, date);Items(itemId, price).
Напишите SQL-запросы для ответа на следующие продуктовые вопросы.
A) Какую сумму в среднем в месяц тратят:
- пользователи в возрастном диапазоне от 18 до 25 лет включительно;
- пользователи в возрастном диапазоне от 26 до 35 лет включительно.
Для решения этой задачи:
- Отбираем пользователей нужных возрастных групп и добавляем информацию, к какой группе относится каждый пользователь.
- Находим все покупки интересующих пользователей и информацию о месяце покупки. Используем приведение даты к первому числу месяца, чтобы избежать разночтений, если покупки были совершены в одинаковые месяцы разных лет.
- Для интересующих пользователей и покупок добавляем информацию о стоимости каждого предмета и суммируем эти данные по пользователям и месяцам.
- Находим среднюю сумму для каждой возрастной группы.
WITH users_18_to_35 AS ( SELECT DISTINCT userId, CASE WHEN age >= 18 AND age <= 25 THEN '18-25' ELSE '26-35' END AS age_group FROM Users WHERE age >= 18 AND age <= 35),users_items AS ( SELECT p.userId, p.itemId, date_trunc('month', p.date)::date AS month FROM Purchases p INNER JOIN users_18_to_35 us ON p.userId = us.userId),total_costs_by_user AS ( SELECT userId, month, SUM(price) AS costs FROM users_items ui INNER JOIN Items i ON i.itemId = ui.itemId GROUP BY userId, month)SELECT AVG(costs) AS avg_costs, age_groupFROM total_costs_by_user tcbuINNER JOIN users_18_to_35 us ON tcbu.userId = us.userIdGROUP BY age_groupБ) В каком месяце года выручка от пользователей в возрастном диапазоне 35+ самая большая.
- Отбираем пользователей старше 35 лет.
- Для них выбираем все покупки, а дату приводим к номеру месяца.
- В финальном запросе находим интересующий месяц и его сумму.
WITH users_from_35 AS ( SELECT DISTINCT userId FROM Users WHERE age >= 35),users_items AS ( SELECT p.userId, p.itemId, DATE_PART('month', p.date) AS month FROM Purchases p INNER JOIN users_from_35 us ON p.userId = us.userId)SELECT month, SUM(price) AS revenueFROM users_items uiINNER JOIN Items i ON i.itemId = ui.itemIdGROUP BY monthORDER BY revenue DESCLIMIT 1В) Какой товар дает наибольший вклад в выручку за последний год.
- Отбираем пользователей, которые совершали покупки в текущем году.
- При помощи оконных функций вычисляем долю затрат на товар от всех затрат.
- Сортируем по убыванию доли.
- Оставляем товар с наибольшим вкладом.
WITH items_current_year AS ( SELECT p.itemId, i.price FROM Purchases p INNER JOIN Items i ON p.itemId = i.itemId WHERE DATE_PART('year', p.date) = DATE_PART('year', CURRENT_DATE))SELECT ic.itemId, SUM(ic.price) OVER (PARTITION BY ic.itemId) AS item_revenue, ROUND( NULLIF( SUM(ic.price) OVER (PARTITION BY ic.itemId), 0 ) / SUM(ic.price) OVER () * 100, 2 ) AS share_of_revenueFROM items_current_year icORDER BY item_revenue DESCLIMIT 1Г) Топ-3 товаров по выручке и их доля в общей выручке за любой год.
- Выбираем товары и даты их покупки. Даты приводим к формату года.
- Для каждого товара находим сумму выручки за год.
- Находим долю каждого товара от общей суммы выручки за год, сортируем по убыванию и оставляем топ-3.
WITH items_per_year AS ( SELECT p.itemId, i.price, DATE_PART('year', p.date) AS year FROM Purchases p INNER JOIN Items i ON p.itemId = i.itemId),items_revenue AS ( SELECT ic.itemId, ic.year, SUM(ic.price) AS item_revenue FROM items_per_year ic GROUP BY ic.itemId, ic.year)SELECT ir.itemId, ir.year, ir.item_revenue, ROUND( NULLIF(ir.item_revenue, 0) / SUM(ir.item_revenue) OVER (PARTITION BY ir.year) * 100, 2 ) AS share_of_revenueFROM items_revenue irORDER BY ir.item_revenue DESCLIMIT 3Готовишься к собеседованию на аналитика?
Посмотри базу реальных тестовых заданий и разборы кейсов.