First-touch атрибуция выручки по каналам

Условие задачи

Есть две таблицы: touches — маркетинговые касания (заходы пользователей по каналам) и conversions — покупки с выручкой. Примени модель атрибуции first-touch: выручку каждой покупки припиши каналу ПЕРВОГО по времени касания этого пользователя. Если у пользователя нет ни одного касания — отнеси покупку к каналу '(direct)'. Выведи колонки: channel — канал; conversions — число покупок, отнесённых к каналу; total_revenue — суммарная выручка канала. Отсортируй по total_revenue по убыванию; при равенстве — по conversions по убыванию, затем по channel по алфавиту.

Схема данных

CREATE TABLE touches (
  user_id INTEGER NOT NULL,
  channel TEXT NOT NULL,      -- organic | ad | email | referral
  touch_time TEXT NOT NULL    -- YYYY-MM-DD HH:MM
);
CREATE TABLE conversions (
  user_id INTEGER NOT NULL,
  revenue INTEGER NOT NULL,
  conv_time TEXT NOT NULL     -- YYYY-MM-DD HH:MM
);

Темы

атрибуция first-touch row_number left-join coalesce

Подсказки

• Первое касание: ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY touch_time) и фильтр rn = 1.
• Строй LEFT JOIN от conversions к первым касаниям — иначе покупки без касаний (direct) потеряются.
• COALESCE(channel,'(direct)') для юзеров без касаний; из-за возможной ничьи по выручке добавь детерминированный tiebreak в ORDER BY (например, channel по алфавиту).

Что проверяет эта задача

Задача «First-touch атрибуция выручки по каналам» учит решать задачи по теме «Маркетинговая атрибуция» так, как это спрашивают на собеседовании аналитика данных. Такие задачи встречаются на собеседованиях в Яндекс и других IT-компаниях. Уровень сложный — задача проверяет глубину и умение собрать запрос из нескольких приёмов.

Как подступиться к решению

Сначала разберись со схемой данных и пойми, какой результат нужен на выходе. Определи, какие таблицы и поля задействованы, и собирай запрос пошагово: фильтрация → соединение или группировка → итоговый отбор и сортировка. Проверь результат на крайних случаях — NULL, дубликаты, пустые группы. Свой запрос сразу прогоняешь автопроверкой в интерактивной SQL-песочнице на реальной базе (SQLite и PostgreSQL 16 прямо в браузере).

Хочешь разобрать SQL системно с нуля — пройди бесплатный курс «SQL с нуля» и закрепи навык в тренажёре.

Решить в интерактивной SQL-песочнице → ← Все SQL-задачи