🎯 Разбор live-coding SQL компании VK

Делаем новую рубрику - разбор заданий с live-coding секций компаний. В этот раз со мной поделились задачей на продуктового аналитика в VK. Актуально на май 2025.

Разберем не просто SQL решение конкретной задачи, а именно методологию, декомпозицию, как себя вести и что делать в трудных ситуациях.

Задача довольно объемная и состоит из трех частей, поэтому разделим историю на несколько постов.

Поехали!

Ты на секции live-coding. У тебя есть онлайн редактор и SQL. Цель — показать не только решение, но и ход мыслей. Показываю, как рассуждать пошагово и писать читаемый код.

📋 Внимательно изучаем условие

У нас есть таблица visits со следующими колонками:

user_id    -- ID пользователя  
campaign   -- канал привлечения  
datetime   -- дата и время визита

🔍 Задача:

Построить таблицу, где указано количество новых пользователей по дням их первого визита. Это и есть когорты привлечения.

🧠 Декомпозиция задачи

Шаг 0 — понять, что такое «когорта привлечения»
Когорта в этом задании — это группа пользователей, у которых первый визит был в один и тот же день.
Например, если 1 марта в первый раз пришли 120 человек, а 2 марта — 90, то у нас две когорты.

Шаг 1 — для каждого пользователя определить дату его первого визита
Это делается с помощью агрегирования:

MIN(datetime) по каждому user_id

Обернём это в DATE(...), чтобы убрать время

Шаг 2 — посчитать, сколько пользователей в каждой дате (когорте)
Просто сгруппируем результаты предыдущего шага по cohort_date

И посчитаем количество строк (пользователей) в каждой дате

Шаг 3 — аккуратно оформить код: используем CTE. Это удобно: видно каждый шаг. Упрощает чтение кода интервьюером

🧱 Сборка кода по частям

🔹 Шаг 1: Сначала — дата первого визита

WITH first_visits AS (
    SELECT 
        user_id,
        MIN(DATE(datetime)) AS cohort_date
    FROM visits
    GROUP BY user_id
)

🔎 Здесь мы для каждого пользователя находим его первую дату визита — и это его когорта.

🔹 Шаг 2: Теперь считаем пользователей по дате когорт

cohort_sizes AS (
    SELECT 
        cohort_date,
        COUNT(*) AS users_count
    FROM first_visits
    GROUP BY cohort_date
)

🔎 Мы группируем пользователей по cohort_date и считаем их количество.

🔹 Шаг 3: Финальный результат

WITH first_visits AS (
    SELECT 
        user_id,
        MIN(DATE(datetime)) AS cohort_date
    FROM visits
    GROUP BY user_id
),
cohort_sizes AS (
    SELECT 
        cohort_date,
        COUNT(*) AS users_count
    FROM first_visits
    GROUP BY cohort_date
)
SELECT *
FROM cohort_sizes
ORDER BY cohort_date;

💡 Общие советы для секции live-coding:

🧩 Решай пошагово. Сначала на бумаге/в голове разбери: «Что мне нужно посчитать? Что известно?»

🗣 Говори вслух. Даже если пишешь простой GROUP BY, комментируй: "Я сейчас группирую по дате привлечения, чтобы получить когорты".

🔤 Пиши чисто. Хорошие имена CTE показывают твоё мышление (first_visits, cohort_sizes, а не cte1).

😌 Думай просто. Интервьюер скорее оценит чёткую структуру, чем «хитрый хак».

📎 В следующем посте: как посчитать ретеншн первой недели по когортам — сколько пользователей вернулись в течение 7 дней после первого визита.

Интересные задачи присылайте мне в личку - разберем. @onlyanalyst

Вопросы по прохождению такой секции, то задавайте в комментариях.

Если пост и формат в целом зайдет (это я пойму по реакциям), то добавим еще и видео решение с подробным объяснением и разными подходами.

😀 @onlyanalystgroup
💬 @onlyanalystchat