ЛокоБанк SQL — выборка для SMS-рассылки по тарифу «амурский тигр»

Условие

Подготовить базу для SMS-рассылки по тарифу «амурский тигр». Доступные таблицы:

• accounts(report_date, acc_num, rest_rub) — счета и остатки на отчётные даты.
• cards(hash_id, tarif_id, date_issued, date_activated, acc_num, client_id) — карты.
• catalog(tarif_id, tarif_name) — справочник тарифов.
• card_status(hash_id, status, date_status) — история статусов карты.
• tel_catalog(client_id, tel_num) — телефоны клиентов.
• black_list(client_id) — клиенты, которым нельзя слать.

Сегмент: владельцы карт с тарифом «амурский тигр», активировавшие карту во 2-м квартале 2016, на 2016-07-01 имеющие остаток на счёте карты ≥ 1000 ₽. Последний статус карты = open. Исключить чёрный список. Если у клиента несколько подходящих карт — взять с максимальным остатком.

Текст SMS:

• Если телефон оканчивается на чётное — обращение по «имя+отчество» + сумма.
• Если на нечётное — «сообщение с суммой остатка карты».
• В каждой группе выделить 15% контроль (не отправлять).

На выходе: client_id, hash_id, rest_rub (округлённый до тысячи), tarif_name, tel_num, sms_text, плюс производные поля (месяц активации 1–12, со 2-го по 4-й символ телефона).

Решение

Подход

Идём по слоям:

1. Карты «амурский тигр», активированные в Q2 2016.
2. Последний статус = open.
3. Остаток на 2016-07-01 ≥ 1000.
4. Исключить black-list.
5. Top-1 карта на клиента по остатку.
6. Текст SMS + контроль 15%.

Реализация (PostgreSQL)

WITH cards_amur AS (
  SELECT c.hash_id, c.acc_num, c.client_id, c.date_activated, t.tarif_name
  FROM cards c
  JOIN catalog t ON t.tarif_id = c.tarif_id
  WHERE LOWER(t.tarif_name) = 'амурский тигр'
    AND c.date_activated BETWEEN DATE '2016-04-01' AND DATE '2016-06-30'
),
last_status AS (
  SELECT hash_id, status,
         ROW_NUMBER() OVER (PARTITION BY hash_id ORDER BY date_status DESC) AS rn
  FROM card_status
),
open_cards AS (
  SELECT ca.*
  FROM cards_amur ca
  JOIN last_status ls
    ON ls.hash_id = ca.hash_id AND ls.rn = 1 AND ls.status = 'open'
),
balance AS (
  SELECT acc_num, rest_rub
  FROM accounts
  WHERE report_date = DATE '2016-07-01'
),
candidates AS (
  SELECT
    oc.client_id, oc.hash_id, oc.tarif_name, oc.date_activated,
    b.rest_rub
  FROM open_cards oc
  JOIN balance b ON b.acc_num = oc.acc_num
  WHERE b.rest_rub >= 1000
    AND oc.client_id NOT IN (SELECT client_id FROM black_list)
),
top_card AS (
  SELECT *
  FROM (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY rest_rub DESC, hash_id) AS rk
    FROM candidates
  ) t
  WHERE rk = 1
),
joined AS (
  SELECT
    tc.client_id, tc.hash_id, tc.tarif_name,
    tc.rest_rub,
    ROUND(tc.rest_rub / 1000.0) * 1000  AS rest_rounded_k,
    EXTRACT(MONTH FROM tc.date_activated)::int AS act_month,
    tel.tel_num,
    SUBSTRING(tel.tel_num, 2, 3) AS tel_2_4,
    -- предполагаем, что в users есть имя+отчество
    cu.first_name, cu.middle_name
  FROM top_card tc
  JOIN tel_catalog tel ON tel.client_id = tc.client_id
  LEFT JOIN clients cu ON cu.client_id = tc.client_id
),
flagged AS (
  SELECT *,
    (CAST(SUBSTRING(tel_num FROM LENGTH(tel_num) FOR 1) AS INT) % 2 = 0) AS is_even,
    -- стабильный hash для контроля 15% - например, по client_id
    (ABS(HASHTEXT(client_id::text)) % 100) AS bucket
  FROM joined
)
SELECT
  client_id, hash_id, rest_rounded_k AS rest_rub, tarif_name, tel_num,
  CASE
    WHEN bucket < 15 THEN NULL  -- контрольная группа, не отправляем
    WHEN is_even
      THEN first_name || ' ' || middle_name ||
           ', на вашей карте ' || rest_rounded_k::text || ' ₽'
    ELSE 'Сообщение с суммой остатка карты: ' || rest_rounded_k::text || ' ₽'
  END AS sms_text,
  CASE WHEN bucket < 15 THEN 1 ELSE 0 END AS is_control
FROM flagged;

Запись в таблицу comms (задача 2)

INSERT INTO comms (client_id, comm_date, days_pass)
SELECT
  client_id,
  CURRENT_DATE,
  EXTRACT(DAY FROM CURRENT_DATE - LAG(CURRENT_DATE) OVER (...)) -- или просто 0 для первой
FROM <результат рассылки>;

days_pass = разница в днях от предыдущей коммуникации до текущей записи — обычно считается на момент новой записи, то есть всегда 0 при добавлении сегодняшней даты, но описание задачи допускает интерпретации.

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

1. «Активирована во 2 квартале 2016» — это апрель–июнь включительно. На границу включаем 2016-04-01 и 2016-06-30.
2. Последний статус через MAX(date_status) не выберет правильный статус — нужно либо ROW_NUMBER, либо DISTINCT ON. Если две записи с одной датой, нужен tie-breaker.
3. «Остаток на 2016-07-01» — точно на эту дату, не «последний до». Если на эту дату записи нет — клиент выпадает; уточнять у бизнеса.
4. Контрольная группа 15%. Чтобы группа была воспроизводимой, используем стабильный хеш от client_id, а не RANDOM().
5. «Чётный/нечётный последний символ» — последний символ может быть '-' или нецифровым в кривых данных. Защититься через регулярку.
6. Top-1 карта при равных остатках — нужен второй ключ сортировки (по hash_id / date_activated), иначе выборка нестабильна.
7. Округление до тысяч. ROUND(x/1000)*1000 — банковское округление в некоторых СУБД. Если нужно «вниз» — FLOOR(x/1000)*1000.

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

Многослойный CTE: тариф → карты в Q2 2016 → ROW_NUMBER для последнего статуса → JOIN остатка на 2016-07-01 → исключить black_list → ROW_NUMBER для top-1 карты на клиента → телефон → текст SMS с условием по чётности + контрольная группа 15% по стабильному хешу.