11 вопросов по теме «аналитика данных: SQL для аналитики» на собеседовании

Короткий ответ: Оконная функция считает агрегат по «окну» строк, но при этом не схлопывает строки — каждая исходная строка сохраняется и получает своё значение. GROUP BY, наоборот, сворачивает группу в одну строку.

Подробно:

1. GROUP BY — N строк группы → 1 строка результата. Деталь теряется.
2. Оконная функция — N строк остаются N строками, рядом появляется агрегат (сумма, ранг, среднее, lag).
3. Синтаксис — func() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY задаёт группы, ORDER BY — порядок внутри окна (нужен для нарастающих итогов и рангов).

-- Нарастающий итог продаж по каждому пользователю
SELECT
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY order_date
  ) AS running_total
FROM orders;

⚠️ Частая ошибка: пытаться отфильтровать по результату оконной функции в WHERE — окна вычисляются после WHERE/GROUP BY, поэтому заворачивайте запрос в CTE/подзапрос и фильтруйте снаружи.

Короткий ответ: Все три нумеруют строки внутри окна, но по-разному ведут себя при равных значениях (ties). Для «топ-3 товара в категории» обычно берут ROW_NUMBER (строго N строк) или RANK/DENSE_RANK, если ничьи должны делить место.

Подробно:

-- Топ-3 товара по выручке в каждой категории
SELECT category, product, revenue
FROM (
  SELECT
    category,
    product,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY revenue DESC
    ) AS rn
  FROM product_sales
) t
WHERE rn <= 3;

⚠️ Частая ошибка: ждать ровно 3 строки от RANK при наличии ничьих — RANK может вернуть больше строк. Если нужно строго N, используйте ROW_NUMBER.

Короткий ответ: CTE (WITH ... AS (...)) выносит подзапрос в именованный блок наверх запроса, поэтому логику читают сверху вниз, а не разворачивая вложенность изнутри наружу. Один CTE можно переиспользовать несколько раз.

Подробно:

1. Читаемость — каждый шаг получает имя; запрос читается как пайплайн, а не как матрёшка из подзапросов.
2. Переиспользование — на CTE можно сослаться несколько раз, на вложенный подзапрос — нет.
3. Рекурсивные CTE — WITH RECURSIVE обходит иерархии и графы (дерево сотрудников, цепочки): базовый запрос + UNION ALL с шагом, ссылающимся на сам CTE.

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS mth,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT mth, revenue,
       revenue - LAG(revenue) OVER (ORDER BY mth) AS mom_change
FROM monthly
ORDER BY mth;

⚠️ Частая ошибка: считать, что CTE всегда ускоряет запрос. Это в первую очередь про читаемость; в части СУБД CTE может материализоваться и помешать оптимизатору.

Короткий ответ: Определите когорту каждого пользователя как месяц его регистрации, затем для каждой активности посчитайте, на сколько месяцев позже неё она произошла, и сгруппируйте по (когорта, смещение месяцев), считая уникальных пользователей.

Подробно:

1. Когорта — DATE_TRUNC('month', signup_date) на пользователя.
2. Смещение — разница в месяцах между месяцем активности и месяцем когорты.
3. Метрика — COUNT(DISTINCT user_id) по (когорта, смещение); делением на размер когорты получаем % удержания.

WITH cohorts AS (
  SELECT user_id,
         DATE_TRUNC('month', signup_date) AS cohort_month
  FROM users
),
activity AS (
  SELECT a.user_id,
         c.cohort_month,
         (DATE_PART('year',  a.event_date) - DATE_PART('year',  c.cohort_month)) * 12
       + (DATE_PART('month', a.event_date) - DATE_PART('month', c.cohort_month)) AS month_offset
  FROM events a
  JOIN cohorts c ON c.user_id = a.user_id
)
SELECT cohort_month,
       month_offset,
       COUNT(DISTINCT user_id) AS active_users
FROM activity
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;

⚠️ Частая ошибка: считать COUNT(*) вместо COUNT(DISTINCT user_id) — несколько событий одного пользователя за месяц раздуют retention.

Короткий ответ: Посчитайте уникальных пользователей, дошедших до каждого шага (через условную агрегацию), затем поделите каждый шаг на предыдущий — это пошаговая конверсия, и на самый первый — это сквозная.

Подробно:

1. Один проход — COUNT(DISTINCT ...) FILTER (WHERE step = '...') даёт количество на каждом шаге без многократных джойнов.
2. Пошаговая конверсия — шаг / предыдущий шаг.
3. Сквозная конверсия — последний шаг / первый шаг.

WITH funnel AS (
  SELECT
    COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit')    AS visited,
    COUNT(DISTINCT user_id) FILTER (WHERE step = 'signup')   AS signed_up,
    COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS purchased
  FROM events
)
SELECT
  visited,
  signed_up,
  purchased,
  ROUND(100.0 * signed_up / NULLIF(visited, 0),   1) AS visit_to_signup_pct,
  ROUND(100.0 * purchased / NULLIF(signed_up, 0), 1) AS signup_to_purchase_pct,
  ROUND(100.0 * purchased / NULLIF(visited, 0),   1) AS overall_pct
FROM funnel;

⚠️ Частая ошибка: не учитывать порядок шагов — пользователь должен пройти именно signup до purchase; для строгой воронки сравнивайте таймстемпы шагов, а не просто факт события.

Короткий ответ: INNER оставляет только совпавшие строки, LEFT — все строки левой таблицы плюс совпадения справа (несовпавшие справа = NULL), FULL OUTER — все строки обеих таблиц. Чтобы найти «осиротевшие» строки, делают LEFT JOIN и фильтруют WHERE right.id IS NULL.

Подробно:

-- Пользователи, которые ни разу не сделали заказ
SELECT u.user_id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
WHERE o.user_id IS NULL;

⚠️ Частая ошибка: ставить условие по правой таблице LEFT JOIN в WHERE (например WHERE o.status = 'paid') — это отбрасывает NULL-строки и превращает LEFT JOIN в INNER. Такое условие переносят в ON.

Короткий ответ: Группируйте по DATE_TRUNC('month', ts) (или 'day'/'week'), чтобы свести события в бакеты. Чтобы в результате не пропадали «пустые» периоды, сгенерируйте полный календарь и LEFT JOIN к нему фактические данные.

Подробно:

1. Бакетирование — DATE_TRUNC обрезает таймстемп до начала периода; группировка по нему даёт ряд по дням/неделям/месяцам.
2. Календарь — generate_series строит непрерывную ось дат.
3. Заполнение дыр — LEFT JOIN факта к календарю + COALESCE(metric, 0), иначе дни без событий просто исчезнут.

WITH calendar AS (
  SELECT generate_series(
    DATE '2024-01-01', DATE '2024-01-31', INTERVAL '1 day'
  )::date AS day
)
SELECT c.day,
       COALESCE(COUNT(o.id), 0) AS orders
FROM calendar c
LEFT JOIN orders o
  ON DATE_TRUNC('day', o.created_at) = c.day
GROUP BY c.day
ORDER BY c.day;

⚠️ Частая ошибка: считать тренд только по строкам с событиями — дни с нулём выпадут, и график/скользящее среднее исказятся.

Короткий ответ: Агрегаты SUM/AVG/COUNT(col) игнорируют NULL. COUNT(*) считает все строки, а COUNT(col) — только строки, где col не NULL. В джойнах и сравнениях NULL «заразен»: NULL = NULL даёт не TRUE, а NULL.

Подробно:

1. Агрегаты — NULL пропускается; важно для AVG: знаменатель — только не-NULL значения, а не все строки.
2. COUNT — COUNT(*) = число строк, COUNT(col) = число не-NULL значений в столбце.
3. COALESCE / NULLIF — COALESCE(x, 0) подставляет значение вместо NULL; NULLIF(a, b) превращает в NULL (часто против деления на ноль: b/NULLIF(a,0)).

SELECT
  COUNT(*)            AS all_rows,      -- все строки
  COUNT(phone)        AS with_phone,    -- только не-NULL телефоны
  AVG(amount)         AS avg_amount,    -- среднее по не-NULL
  AVG(COALESCE(amount, 0)) AS avg_with_zeros  -- NULL как 0
FROM users;

⚠️ Частая ошибка: считать долю заполненности через COUNT(col)/COUNT(col) или сравнивать с NULL через = NULL. Для проверки используйте IS NULL / IS NOT NULL.

Короткий ответ: Медиана и перцентили — это PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x). Для разбиения на равные доли (квартили, децили) удобен NTILE(n). AVG прячет перекос: один выброс утягивает среднее, а медиана устойчива.

Подробно:

1. Перцентили — PERCENTILE_CONT(0.5) (медиана), 0.9 (p90), 0.95 (p95); интерполирует между значениями.
2. NTILE — NTILE(4) OVER (ORDER BY x) раскладывает строки по 4 равным бакетам (квартили).
3. Почему не AVG — для скошенных величин (выручка, время отклика) среднее завышено хвостом; смотрят медиану и p90/p95.

SELECT
  PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY response_ms) AS median_ms,
  PERCENTILE_CONT(0.9)  WITHIN GROUP (ORDER BY response_ms) AS p90_ms,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_ms) AS p95_ms,
  AVG(response_ms)                                          AS mean_ms
FROM requests;

⚠️ Частая ошибка: отчитываться только средним по правоскошенным данным — p95 покажет реальный «хвост», который видят пользователи, а среднее его замаскирует.

Короткий ответ: Оборачивайте CASE WHEN в SUM или COUNT, чтобы в одном проходе посчитать несколько метрик по разным условиям — это «pivot» строк в столбцы без отдельных запросов на каждую группу.

Подробно:

1. SUM(CASE ...) — SUM(CASE WHEN cond THEN amount ELSE 0 END) суммирует только подходящие строки.
2. COUNT(CASE ...) — COUNT(CASE WHEN cond THEN 1 END) считает строки по условию (ELSE даёт NULL, который COUNT пропускает).
3. FILTER — в PostgreSQL читабельнее: COUNT(*) FILTER (WHERE cond).

-- Метрики по месяцам: разворачиваем статусы в столбцы
SELECT
  DATE_TRUNC('month', created_at) AS mth,
  COUNT(*)                                       AS total,
  SUM(CASE WHEN status = 'paid'     THEN amount ELSE 0 END) AS paid_revenue,
  COUNT(*) FILTER (WHERE status = 'refunded')    AS refunds
FROM orders
GROUP BY 1
ORDER BY 1;

До (строки)            После (столбцы)
mth   status           mth   paid  refunds
01    paid       →      01    120   3
01    refunded         02    150   1

⚠️ Частая ошибка: писать COUNT(CASE WHEN cond THEN 0 END) — ноль не NULL, поэтому посчитаются все строки. Возвращайте 1/значение, а в ELSE — NULL.

Короткий ответ: Индексы ускоряют выборочные фильтры и джойны, но почти бесполезны для полносканных агрегаций по всей таблице. Избегайте SELECT *, фильтруйте раньше, заранее агрегируйте, а DISTINCT и широкий GROUP BY — дорогие из-за сортировки/хеширования.

Подробно:

1. Индексы — выигрыш на узких фильтрах (WHERE dt >= ...) и ключах джойна; на агрегации «по всему» оптимизатор всё равно выберет seq scan.
2. **SELECT *** — тащит лишние столбцы, рушит index-only scan и раздувает I/O; перечисляйте только нужное.
3. Предагрегация — материализованные представления / rollup-таблицы считают тяжёлое заранее.
4. DISTINCT / большой GROUP BY — требуют сортировки или хеша; на больших объёмах это узкое место. EXPLAIN ANALYZE покажет реальный план.

-- Узкий фильтр + только нужные столбцы → пригоден index-only scan
CREATE INDEX idx_orders_created ON orders (created_at);

EXPLAIN ANALYZE
SELECT DATE_TRUNC('day', created_at) AS day, SUM(amount)
FROM orders
WHERE created_at >= DATE '2024-01-01'
GROUP BY 1;

⚠️ Частая ошибка: оборачивать индексированный столбец в функцию в WHERE (WHERE DATE(created_at) = ...) — это убивает индекс. Сравнивайте с диапазоном: created_at >= ... AND created_at < ....