Ренессанс Кредит SQL — выборка для рассылки по партнёру с RFM-сегментами

Условие

Есть три таблицы:

• clients(ow_id, birth_dt, email_flg, mobile_app_flg) — справочник клиентов (исторический, с дублями, нужен последний срез).
• comms(ow_id, sent_date, channel_nm, partner_id, sent, delivered, opened, clicked, cell_pk) — история коммуникаций.
• purchases(ow_id, partner_id, pos, cash_dt) — покупки у партнёров.

Составить выборку для рассылки email по партнёру partner_id = 1111:

1. Клиент не получал коммуникации по этому партнёру за прошлую неделю ни по одному каналу.
2. Сегментировать клиентов по тратам у партнёра 1111 за последние 30 дней:
   • NEW — не покупал.
   • SMALL — суммарно ≤ 1000 ₽.
   • BIG — суммарно > 1000 ₽.
3. На выходе: client_id, segment.

Решение

Подход

1. Берём клиентов, у которых есть email (флаг с актуального среза).
2. Anti-join к коммуникациям прошлой недели по партнёру 1111.
3. Левое присоединение покупок за последние 30 дней + CASE WHEN для сегмента.

Реализация

WITH last_client AS (
  -- Последняя версия записи о клиенте (если есть updated_at, лучше брать по нему;
  -- иначе - по дате коммуникации или просто DISTINCT с COALESCE по флагам)
  SELECT
    ow_id,
    MAX(email_flg)      AS email_flg,
    MAX(mobile_app_flg) AS mobile_app_flg
  FROM clients
  GROUP BY ow_id
),
recent_comms AS (
  SELECT DISTINCT ow_id
  FROM comms
  WHERE partner_id = 1111
    AND sent_date >= CURRENT_DATE - INTERVAL '7 day'
),
spend_30d AS (
  SELECT ow_id, SUM(pos) AS total_pos
  FROM purchases
  WHERE partner_id = 1111
    AND cash_dt >= CURRENT_DATE - INTERVAL '30 day'
  GROUP BY ow_id
)
SELECT
  c.ow_id AS client_id,
  CASE
    WHEN s.total_pos IS NULL    THEN 'NEW'
    WHEN s.total_pos <= 1000    THEN 'SMALL'
    ELSE                              'BIG'
  END AS segment
FROM last_client c
LEFT JOIN spend_30d s ON s.ow_id = c.ow_id
WHERE c.email_flg = 1
  AND c.ow_id NOT IN (SELECT ow_id FROM recent_comms);

Ключевые моменты

• NOT IN vs LEFT JOIN ... WHERE NULL vs NOT EXISTS — в большинстве СУБД NOT EXISTS безопаснее всего: он корректно работает с NULL в подзапросе.
• Условие «ни по одному каналу» уже учтено: в recent_comms мы не фильтруем по channel_nm и берём DISTINCT ow_id.

Альтернатива через NOT EXISTS

WHERE c.email_flg = 1
  AND NOT EXISTS (
    SELECT 1 FROM comms cm
    WHERE cm.ow_id = c.ow_id
      AND cm.partner_id = 1111
      AND cm.sent_date >= CURRENT_DATE - INTERVAL '7 day'
  )

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

1. Дубли в clients. В исходных данных одна запись клиента дублируется с разными значениями флагов (видимо, snapshot’ы). Без агрегации легко получить дубли в финальной выборке.
2. NOT IN + NULL. Если в подзапросе recent_comms появится строка с ow_id IS NULL, NOT IN выдаст пустоту. NOT EXISTS устойчив к этому.
3. Часовой пояс / граница 30 дней. «Последние 30 дней» — это >= CURRENT_DATE - 29 или >= CURRENT_DATE - 30? Уточнять у заказчика.
4. «Не получал коммуникации» — а если пуш отправлен, но не доставлен? Условие задачи звучит как sent_date IS NOT NULL, не зависит от delivered. Но обычно для повторной коммуникации логичнее «доставлен или открыт» — обсудить.
5. CASE порядок ветвей. WHEN total_pos <= 1000 должно идти после проверки на NULL — иначе клиенты-NEW попадут в SMALL не попадут (NULL <= 1000 = NULL = false), но безопаснее явно разделить.

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

Anti-join к коммуникациям прошлой недели по партнёру 1111 → LEFT JOIN агрегата покупок за 30 дней → CASE WHEN → 3 сегмента (NEW / SMALL / BIG).