SQL для продуктовых метрик за 5 минут

Аналитику и продакту на собесе часто дают таблицу событий или заказов и просят посчитать метрику. Ниже приёмы на PostgreSQL, которые закрывают большинство задач.

Уникальные пользователи

SELECT COUNT(DISTINCT user_id) FROM events WHERE event = 'purchase';

Почти любая продуктовая метрика считается по людям, а не по событиям.

Доля через FILTER

SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE amount > 2000) / COUNT(*), 1) AS big_share
FROM orders;

100.0 спасает от целочисленного деления: 1 / 3 в PostgreSQL даёт 0.

Сначала до пользователя, потом до группы

Классическая ошибка: соединить пользователей с заказами и сразу считать COUNT(*). Получатся заказы, а не люди. Правильно сначала свернуть заказы до пользователя в CTE, потом агрегировать по группе.

WITH per_user AS (
  SELECT user_id, COUNT(*) AS orders, SUM(amount) AS revenue
  FROM orders GROUP BY user_id
)
SELECT COUNT(*) FILTER (WHERE orders >= 2) * 100.0 / COUNT(*) AS repeat_rate
FROM per_user;

Первое событие пользователя

MIN(created_date) или ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_date). Второй вариант позволяет отличить первую покупку от повторных.

Период к периоду

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS change_pct
FROM monthly;

Нарастающий итог

SUM(revenue) OVER (ORDER BY month) даёт накопленную сумму.

Даты

• date_trunc('month', d)::date первый день месяца;
• d2 - d1 для date даёт число дней;
• generate_series строит календарь, чтобы не потерять дни без событий.

Частые ошибки

• COUNT(*) после JOIN вместо COUNT(DISTINCT ...).
• Целочисленное деление.
• INNER JOIN теряет пользователей без заказов, и они пропадают из знаменателя.
• Пропущенные дни без событий завышают средний DAU.

Что спросят на собесе

• Посчитайте долю повторных покупателей.
• Посчитайте изменение выручки месяц к месяцу.
• Найдите время до первой покупки.
• Почему ваш запрос считает заказы, а не пользователей?