SQL JOIN для аналитика: как соединять users, events и payments

Коротко

JOIN соединяет строки из двух источников по ключу. Для продуктовой аналитики важно помнить не только условие `ON`, но и кардинальность: одна строка users может соединиться с несколькими events или payments.

• `INNER JOIN` оставляет только совпавшие строки с обеих сторон.
• `LEFT JOIN` сохраняет все строки из таблицы слева и добавляет совпадения справа.
• До JOIN зафиксируй grain каждой таблицы и ожидаемый grain результата.
• После соединения проверь `COUNT(*)` и `COUNT(DISTINCT user_id)`.
• Две таблицы one-to-many лучше сначала агрегировать до нужного grain, а потом соединять.

Сначала нарисуй связи

В простом учебном наборе `users.user_id` — ключ пользователя. В `events` один пользователь может иметь много строк, потому что одна строка означает одно событие. В `payments` тоже может быть несколько платежей на пользователя. Поэтому связь users → events и users → payments имеет тип one-to-many.

Если соединить users сразу и с events, и с payments, пользователь с тремя событиями и двумя платежами даст шесть строк промежуточного результата. Это не “ошибка JOIN”: база честно показала все комбинации. Ошибка появляется, когда эти строки затем принимают за уникальных пользователей или складывают платежи повторно.

INNER JOIN: оставить только совпадения

Начнём с вопроса: какие пользователи сделали core action? Для этого достаточно соединить users с отфильтрованными events. Пользователи без подходящего события в результат не попадут — это и есть поведение INNER JOIN.

select distinct
  u.user_id,
  u.channel,
  e.event_date
from users as u
join events as e
  on e.user_id = u.user_id
where e.event_name = 'core_action'
order by e.event_date, u.user_id;

LEFT JOIN: не потерять пользователей без событий

Теперь другой вопрос: сколько пользователей из каждого канала вообще не сделали core action? Здесь users должна остаться главной таблицей, поэтому используем LEFT JOIN. Строки без совпадения справа сохранятся, а поля events будут равны NULL.

Обрати внимание, где стоит условие `e.event_name = ...`. Если поставить его в WHERE, строки с NULL справа будут отброшены, и LEFT JOIN фактически начнёт вести себя как INNER JOIN. Условия по правой таблице, которые должны сохранить “пустые” совпадения, обычно ставят в ON.

select
  u.user_id,
  u.channel,
  e.event_date
from users as u
left join events as e
  on e.user_id = u.user_id
 and e.event_name = 'core_action'
order by u.user_id, e.event_date;

Агрегируй до JOIN, если считаешь деньги

Самая надёжная защита от размножения — сначала привести каждую таблицу к зерну, которое нужно в результате. Например, сначала посчитать платежи по пользователю, а события — отдельно по пользователю, и только потом присоединить обе сводки к users.

Перед финальным SELECT обе промежуточные таблицы имеют grain “один пользователь”. Поэтому JOIN больше не создаёт комбинации “каждый платёж × каждое событие”. `COALESCE` превращает отсутствие платежей или событий в ноль, чтобы результат было удобно читать в BI.

with payments_by_user as (
  select
    user_id,
    sum(amount) as revenue
  from payments
  group by user_id
),
events_by_user as (
  select
    user_id,
    count(*) filter (where event_name = 'core_action') as core_actions
  from events
  group by user_id
)
select
  u.user_id,
  u.channel,
  coalesce(p.revenue, 0) as revenue,
  coalesce(e.core_actions, 0) as core_actions
from users as u
left join payments_by_user as p using (user_id)
left join events_by_user as e using (user_id)
order by revenue desc, u.user_id;

Как найти размножение строк

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

select
  u.user_id,
  count(*) as joined_rows,
  count(distinct e.event_date) as active_days,
  count(distinct p.paid_at) as payment_days
from users as u
left join events as e using (user_id)
left join payments as p using (user_id)
group by u.user_id
having count(*) > 1
order by joined_rows desc;

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

JOIN ломается не только из-за неправильного синтаксиса. Чаще запрос запускается успешно, но меняет смысл метрики из-за неверной стороны LEFT JOIN, дублирующего ключа или фильтра в неправильном месте.

• Соединить две one-to-many таблицы напрямую и посчитать сумму после размножения строк.
• Использовать INNER JOIN, когда нужны и пользователи без событий или платежей.
• Поставить фильтр правой таблицы в WHERE и потерять строки без совпадений.
• Соединить по неуникальному полю вроде channel вместо user_id.
• Не учитывать временную логику: событие должно произойти до платежа, а не когда-нибудь в истории.
• Считать COUNT(*) как количество пользователей после соединения с events.

Мини-практика и следующий шаг

Возьми users и events в SQL-тренажёре. Сначала посчитай пользователей с core action через JOIN, затем перепиши запрос на LEFT JOIN и найди тех, у кого события нет. После этого добавь payments и сравни результат с вариантом, где платежи агрегированы заранее.