Разбор задач с собеседования на SQL в Альфа-Банк
Условие задач
Даны три связанные таблицы. Сначала попробуй написать запросы сам, а после — сверься с эталонным решением.
users ( user_id, created_at DATETIME, country: France, Germany.. registration_platform: web, ios.. channel_by_trail: sms_pushing_01..)users_actions ( user_id, action_id)actions ( action_id, action_type: view_offer, apply_loan.. success_type BOOL, seconds_from_last_action INTEGER, created_at DATETIME)Задача 1. Уникальные и успешные действия
Считаем количество уникальных действий через JOIN users_actions с actions, а количество успешных — через FILTER (WHERE).
SELECT ua.user_id, count(distinct ua.action_id) AS total_actions, count(distinct ua.action_id) FILTER ( WHERE a.success_type = true ) AS successful_actionsFROM users_actions AS uaJOIN actions AS a ON ua.action_id = a.action_idGROUP BY ua.user_idORDER BY ua.user_idЗадача 2. Среднее время между действиями
Для каждого пользователя считаем целое среднее время (в секундах) между действиями, если таких действий больше одного. Фильтруем только seconds_from_last_action IS NOT NULL и округляем результат через round.
SELECT ua.user_id, round(avg(a.seconds_from_last_action)) AS avg_seconds_between_actionsFROM users_actions AS uaJOIN actions AS a ON ua.action_id = a.action_idWHERE a.seconds_from_last_action IS NOT NULLGROUP BY ua.user_idORDER BY ua.user_idЗадача 3. Заявка без успешного просмотра оффера
Ищем пользователей, кто успешно подал заявку (apply_loan), но у кого до этого не было успешного просмотра оффера (view_offer) по времени created_at.
SELECT DISTINCT ua.user_idFROM users_actions AS uaJOIN actions AS a ON ua.action_id = a.action_idWHERE a.action_type = 'apply_loan' AND a.success_type = true AND NOT EXISTS ( SELECT * FROM users_actions AS ua2 JOIN actions AS a2 ON ua2.action_id = a2.action_id WHERE ua2.user_id = ua.user_id AND a2.action_type = 'view_offer' AND a2.success_type = true AND a2.created_at < a.created_at )Задача 4. Пользователи и среднее по каналам
Для каждого канала привлечения (channel_by_trail) считаем число пользователей хотя бы с одним успешным действием и среднее число успешных действий на пользователя. Используем два CTE.
WITH successful_actions AS ( SELECT ua.user_id, a.action_id FROM users_actions AS ua JOIN actions AS a ON ua.action_id = a.action_id WHERE a.success_type = true),user_success_count AS ( SELECT user_id, count(*) AS success_count FROM successful_actions GROUP BY user_id)SELECT u.channel_by_trail, count(DISTINCT usc.user_id) AS users_with_success, round(avg(usc.success_count), 2) AS avg_success_per_userFROM user_success_count AS uscJOIN users AS u ON usc.user_id = u.user_idGROUP BY u.channel_by_trailВ карточках выше ты найдешь 4 задания на SQL с собеседования в Альфа-Банк для проверки себя. Сначала попробуй составить запросы сам, после – сверяйся с моим вариантом решения.
Как вы возможно догадываетесь, большинство заданий для разборов я беру либо с собеседований своих менти, либо со своего большого сборника тестовых заданий:
https://nodatanogrowth.com/test-tasks
Готовишься к собеседованию на аналитика?
Посмотри базу реальных тестовых заданий и разборы кейсов.