Условие

Таблица linkedin_job_events(user_id, job_id, event_type, event_time) — этапы: view, click, apply, complete. Постройте воронку:

Этап	Уникальных пользователей	Доля от view	Доля от пред. шага

Решение

Шаг 1 — собрать уникальных юзеров по этапу

WITH stage_users AS (
  SELECT event_type AS stage,
         COUNT(DISTINCT user_id) AS users
  FROM linkedin_job_events
  WHERE event_type IN ('view', 'click', 'apply', 'complete')
  GROUP BY event_type
),

Шаг 2 — упорядочить этапы и посчитать доли

ordered AS (
  SELECT
    stage,
    users,
    CASE stage
      WHEN 'view' THEN 1
      WHEN 'click' THEN 2
      WHEN 'apply' THEN 3
      WHEN 'complete' THEN 4
    END AS step_no
  FROM stage_users
),
withref AS (
  SELECT
    stage, users, step_no,
    FIRST_VALUE(users) OVER (ORDER BY step_no) AS view_users,
    LAG(users) OVER (ORDER BY step_no) AS prev_users
  FROM ordered
)
SELECT
  stage,
  users,
  ROUND(users::DECIMAL / view_users * 100, 2) AS pct_of_view,
  ROUND(users::DECIMAL / NULLIF(prev_users, 0) * 100, 2) AS pct_of_prev
FROM withref
ORDER BY step_no;

Важно: «воронка» с возвратами

Юзер сделал view → click → view → click → apply — на этапе view он один раз, на click один раз, на apply один раз. COUNT(DISTINCT user_id) это и считает. Если же интервью просит именно последовательную воронку (juser должен пройти все этапы по порядку, без пропусков) — нужен другой запрос со self-JOIN по event_time.

-- Строгая воронка: юзер сделал view, потом click, потом apply
WITH steps AS (
  SELECT user_id, event_type, event_time,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
  FROM linkedin_job_events
)
-- Дальше: проверять, что rn(click) > rn(view), и т.д.

Подводные камни

COUNT(DISTINCT user_id) не учитывает порядок. Если у юзера был только apply без view — он попадёт в шаг apply, что может быть бизнесово некорректно. Строгие воронки требуют последовательного фильтра.
Категория «дроп» между шагами. (view - click) / view * 100 — drop rate. Иногда просят именно его.
Множественные воронки на одну сессию. Юзер 3 раза кликнул, 2 раза applied — без DISTINCT задвоится. С DISTINCT — 1 на этапе.

Эталонный ответ

COUNT(DISTINCT user_id) GROUP BY event_type, потом FIRST_VALUE для нормализации к view и LAG для шага. Объяснить разницу «нестрогая vs строгая» воронка — большой плюс на собесе.

StrataScratch (LinkedIn) — воронка отклика на вакансию