Условие

Таблица orders(user_id, order_date, amount). Сегментируйте пользователей по RFM:

Recency — дни с последнего заказа,
Frequency — число заказов,
Monetary — суммарный amount.

Для каждой метрики — квинтиль 1..5 (5 — лучший). Финальный сегмент — конкатенация R-F-M (например 5-4-5). Для каждого сегмента покажите число пользователей.

Решение

Шаг 1 — собрать метрики на user

WITH rfm_raw AS (
  SELECT
    user_id,
    CURRENT_DATE - MAX(order_date) AS recency_days,
    COUNT(*) AS frequency,
    SUM(amount) AS monetary
  FROM orders
  GROUP BY user_id
),

Шаг 2 — NTILE(5) для каждой метрики

scored AS (
  SELECT
    user_id,
    -- Чем меньше recency_days, тем лучше → ASC → топ-квинтиль = 5
    6 - NTILE(5) OVER (ORDER BY recency_days ASC) AS r,
    NTILE(5) OVER (ORDER BY frequency DESC) AS f,
    NTILE(5) OVER (ORDER BY monetary DESC) AS m
  FROM rfm_raw
)
SELECT
  CONCAT(r, '-', f, '-', m) AS segment,
  COUNT(*) AS users
FROM scored
GROUP BY r, f, m
ORDER BY users DESC;

Тонкость с recency

«Меньше дней» = «лучше». NTILE по возрастанию даёт 1 для лучших, 5 для худших. Чтобы «5 = лучший», инвертируем: 6 - NTILE(5). Альтернативно — NTILE по убыванию recency_days DESC, но это менее интуитивно.

Какие сегменты «золотые»

5-5-5 — лучшие («Champions»).
5-1-1 — недавно зашёл, мало покупает (потенциал онбординга).
1-5-5 — лучшие в прошлом, не возвращаются (риск churn).

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

NTILE не учитывает ties. Два юзера с одинаковой frequency могут попасть в разные квинтили — зависит от внутреннего порядка. Для строгости использовать NTILE с детерминированным ORDER BY (например ORDER BY frequency DESC, user_id).
NTILE на малых выборках. 100 юзеров → по 20 в квинтиле. < 5 юзеров — NTILE раздаст по 1.
CURRENT_DATE - MAX(order_date) в Postgres даёт INTERVAL, в MySQL — int (дни через DATEDIFF).
Юзер без заказов. Не появится в rfm_raw (LEFT JOIN из users → orders нужен, если их хотим включить как сегмент «0-0-0» / «inactive»).

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

NTILE(5) OVER (ORDER BY ...) для каждой из R/F/M, инверсия для recency, конкатенация в строку. Сегменты — это уже бизнес-словарь.

InterviewQuery — RFM-сегментация через NTILE(5)