Условная агрегация в SQL: CASE, FILTER и метрики в одном запросе

Симптом в отчёте

Как посчитать конверсию и сегменты рядом, не создавая десять почти одинаковых запросов?

В отчёте по signup можно одновременно посчитать новых пользователей, тех, кто дошёл до activation, и тех, кто оплатил. Главное — определить окно: activation в день регистрации, в течение семи дней или когда угодно после неё. SQL не решит эту методологическую развилку за команду.

Что на самом деле делает SQL

CASE WHEN превращает условие в значение, которое можно агрегировать. FILTER синтаксически отделяет условие конкретного агрегата и хорошо читается в PostgreSQL. Для конверсии важно считать пользователей или сущности в числителе и знаменателе на одном уровне, а не делить количество событий на количество пользователей случайно.

Сначала собери базовую когорту в CTE. Затем добавь условные счётчики и деления через NULLIF. Для пользовательских метрик используй COUNT(DISTINCT user_id), если поток событий может повторяться. После запроса проверь, что подмножества не используют разные даты.

• Зафиксируй одну строку результата.
• Отдели условия по исходным строкам от условий по рассчитанным значениям.
• Назови период, ключи связи и правило для повторов.

Исправленный запрос

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

WITH cohort AS (
  SELECT user_id, DATE_TRUNC('week', registered_at) AS week
  FROM users
  WHERE registered_at >= DATE '2026-01-01'
)
SELECT week, COUNT(DISTINCT user_id) AS users,
       COUNT(DISTINCT user_id) FILTER (WHERE activated_at < registered_at + INTERVAL '7 days') AS activated,
       COUNT(DISTINCT user_id) FILTER (WHERE first_paid_at IS NOT NULL) AS paid
FROM cohort GROUP BY week ORDER BY week;

Контрольные строки

Собери пять-десять строк, где ответ можно получить вручную. Добавь NULL, дубль, объект без связанной записи и значение на границе периода. Затем сравни число строк и уникальных ключей до и после спорного шага.

Выполненный без ошибки запрос ещё не является правильным. Другой аналитик должен суметь восстановить из него источник, фильтр, grain и ожидаемое поведение на крайних случаях.

Где появится ошибка

CASE без ELSE возвращает NULL, что влияет на SUM и COUNT. COUNT(CASE WHEN ...) и SUM(CASE WHEN ... ELSE 0 END) похожи, но читаются по-разному. Не считай conversion как AVG(event_flag), если одна строка — событие, а не пользователь.

CASE удобен для переносимого SQL и сложных ветвлений. FILTER делает PostgreSQL-запрос компактнее, когда у каждого агрегата своё условие. В обоих случаях сначала фиксируй базовую когорту и зерно, затем считай показатели.

Попробуй на своей схеме

Собери одну таблицу по неделям с total_users, activated_users, paid_users, mobile_users и error_users. Затем добавь guardrail: долю отмен. Проверь результат на маленьком наборе данных вручную и сравни с независимым запросом по каждой метрике.

• Сначала запиши ожидаемый результат словами.
• Проверь запрос на маленькой контрольной выборке.
• Объясни, что запрос считает и чего не доказывает.

Что оставить в документации

CASE удобен для переносимого SQL и сложных ветвлений. FILTER делает PostgreSQL-запрос компактнее, когда у каждого агрегата своё условие. В обоих случаях сначала фиксируй базовую когорту и зерно, затем считай показатели.