Органик → реферал подряд

Условие задания

**Контекст:** Задание по мотивам реального тестового в ivi. Маркетинговый аналитик ищет пользователей, которые пришли органически, а следующий их визит был по реферальной ссылке — это сигнал эффективности «сарафанного радио».

**Данные:**

[см. код в задании]

| user_id | content_id | show_date | utm_medium |
|---------|------------|---------------------|------------|
| 1 | 10 | 2025-03-01 10:00:00 | organic |
| 1 | 11 | 2025-03-01 10:30:00 | organic |
| 1 | 12 | 2025-03-02 14:00:00 | referral |
| 2 | 10 | 2025-03-01 08:00:00 | organic |
| 2 | 14 | 2025-03-01 08:45:00 | referral |
| ... | ... | ... | ... |

**Задание:**

1. Найдите пользователей, у которых organic-просмотр непосредственно сменился referral-просмотром (без других визитов между ними)
2. Выведите user_id, show_date органик-визита, show_date реферального визита
3. Если у пользователя несколько таких пар — выведите все

Пример данных

Структура для ориентира — реальные значения из эталонного решения.

CREATE TABLE content_shows (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    content_id INT NOT NULL,
    show_date TIMESTAMP NOT NULL,
    utm_medium VARCHAR(50)           -- 'organic', 'referral', 'cpc', 'email', NULL
);

INSERT INTO content_shows (user_id, content_id, show_date, utm_medium) VALUES
(1, 10, '2025-03-01 10:00:00', 'organic'),
(1, 11, '2025-03-01 10:30:00', 'organic'),
(1, 12, '2025-03-02 14:00:00', 'referral'),
(1, 13, '2025-03-03 09:00:00', 'cpc'),
(2, 10, '2025-03-01 08:00:00', 'organic'),
(2, 14, '2025-03-01 08:45:00', 'referral'),
(2, 15, '2025-03-02 12:00:00', 'organic'),
(2, 16, '2025-03-02 18:00:00', 'email'),
(3, 10, '2025-03-01 20:00:00', 'cpc'),
(3, 11, '2025-03-02 21:00:00', 'organic'),
(3, 12, '2025-03-03 22:00:00', 'referral'),
(4, 10, '2025-03-01 11:00:00', 'organic'),
(4, 11, '2025-03-01 15:00:00', 'organic'),
(5, 10, '2025-03-02 09:00:00', 'referral'),
(5, 11, '2025-03-03 10:00:00', 'organic');

Темы

LEAD оконные функции последовательность utm

Подсказки

• LEAD(utm_medium) OVER (PARTITION BY user_id ORDER BY show_date) покажет utm следующего визита
• Фильтр: utm_medium = 'organic' AND next_utm = 'referral'
• LEAD(show_date) OVER (...) покажет время следующего визита для вывода

Все тестовые задания →

Частые вопросы

Какой уровень знаний нужен для задачи "Органик → реферал подряд"?

Это задание для уровня Senior. Senior-уровень — глубокое понимание темы, опыт решения нестандартных задач, обсуждение trade-off на собеседовании.

На каких собеседованиях встречается такая задача?

Подобные задания в категории «SQL» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: LEAD, оконные функции, последовательность, utm.

Сколько времени даётся на решение?

На реальном собеседовании на подобную задачу отводится 30-60 минут с обсуждением подходов, оптимизаций и trade-off. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.

Где ещё потренироваться по теме «SQL»?

На zasqlpython.ru есть 425 SQL задач в песочнице с автопроверкой кода, конспекты SQL для аналитика, AI мок-собеседование с разбором ваших ответов.

← Все задания