ВТБ SQL — отток клиентов и средний ЧОД по сегментам и продуктам

Условие

Есть две таблицы в SQLite-базе УЖЦК (Управление по работе с малым и средним бизнесом):

pl(client_id, segment, period, product, pl) — ЧОД (чистый операционный доход) клиента по продукту в каждом периоде. Если строки нет — клиент в этом периоде продуктом не пользовался.

campaign(client_id, product, response, manager_id) — данные маркетинговой кампании, проведённой между периодами p_1 и p_2.

Нужно (часть полной задачи):

1. Посчитать средний ЧОД по продуктам и количество клиентов с продуктом для каждого сегмента в разрезе периодов.
2. Оценить отток клиентов с продукта в периоде p_2.

Решение

Задача 1 — средний ЧОД и число клиентов

SELECT
  segment,
  period,
  product,
  AVG(pl)              AS avg_pl,
  COUNT(DISTINCT client_id) AS clients_cnt
FROM pl
GROUP BY segment, period, product
ORDER BY segment, period, product;

AVG(pl) считает среднее по строкам — это и есть «средний ЧОД на клиента-продукт» при гарантии 1 строки на клиент-продукт-период.

Задача 2 — отток в p_2

«Отток с продукта» = клиенты, которые были с продуктом в p_1 и отсутствуют в p_2 (нет строки в pl для пары клиент-продукт).

WITH p1 AS (
  SELECT DISTINCT client_id, product
  FROM pl
  WHERE period = 'p_1'
),
p2 AS (
  SELECT DISTINCT client_id, product
  FROM pl
  WHERE period = 'p_2'
),
churned AS (
  SELECT p1.client_id, p1.product
  FROM p1
  LEFT JOIN p2
    ON p2.client_id = p1.client_id
   AND p2.product   = p1.product
  WHERE p2.client_id IS NULL
)
SELECT
  product,
  COUNT(*)                                        AS churned_cnt,
  (SELECT COUNT(*) FROM p1 WHERE product = c.product) AS p1_cnt,
  ROUND(100.0 * COUNT(*) /
        (SELECT COUNT(*) FROM p1 WHERE product = c.product), 2) AS churn_rate_pct
FROM churned c
GROUP BY product;

Анти-join альтернатива

SELECT
  product,
  COUNT(DISTINCT client_id) AS churned_cnt
FROM pl p1
WHERE period = 'p_1'
  AND NOT EXISTS (
    SELECT 1 FROM pl p2
    WHERE p2.client_id = p1.client_id
      AND p2.product   = p1.product
      AND p2.period    = 'p_2'
  )
GROUP BY product;

С разбивкой по сегментам

SELECT
  p1.segment,
  p1.product,
  COUNT(DISTINCT p1.client_id) AS p1_clients,
  COUNT(DISTINCT CASE WHEN p2.client_id IS NULL THEN p1.client_id END) AS churned,
  ROUND(100.0 *
        COUNT(DISTINCT CASE WHEN p2.client_id IS NULL THEN p1.client_id END) /
        NULLIF(COUNT(DISTINCT p1.client_id), 0), 2) AS churn_rate_pct
FROM (SELECT * FROM pl WHERE period = 'p_1') p1
LEFT JOIN (SELECT DISTINCT client_id, product FROM pl WHERE period = 'p_2') p2
  ON p2.client_id = p1.client_id
 AND p2.product   = p1.product
GROUP BY p1.segment, p1.product;

Что значит «отток»

• Жёсткий отток: клиент полностью ушёл из банка (нет строк в p_2 ни по одному продукту).
• Продуктовый отток: ушёл с конкретного продукта, но остался в банке.

В задаче формулировка про продуктовый, но при защите ответа стоит упомянуть оба определения и показать оба числа.

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

1. Сегмент в p_1 vs p_2. Сегмент клиента может меняться. Считаем отток в разрезе сегмента на момент p_1 — это входная сегментация.
2. «Не было в p_1, появился в p_2» — это не отток, а новый клиент / cross-sell. Не путать.
3. Отсутствие в p_2 ≠ отток с продукта. Возможно, продукт переоформлен/перенумерован (новый product_id). В реальности нужно учитывать перетоки.
4. COUNT(DISTINCT) важен. Если у клиента несколько строк по продукту в p_1 (например, разные субпродукты), COUNT(*) посчитает дубли.
5. NULLIF в знаменателе — защита от деления на 0.

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

(1) Простая агрегация AVG(pl), COUNT(DISTINCT client_id) GROUP BY segment, period, product. (2) Анти-join: клиенты-продукты из p_1, для которых нет строки в p_2. Считаем churn_rate = churned / p1_total. Желательно дать срез по сегменту.