PrepBro
← Назад к вопросам

SQL: Процентиль выручки по клиентам

2.0 Middle🔥 161 комментариев
#SQL и базы данных#Статистика и математика

Условие

У вас есть таблица orders:

  • order_id (integer)
  • customer_id (integer)
  • amount (decimal)

Разбейте клиентов на квартили по общей сумме заказов.

Ожидаемый результат:

Таблица с колонками:

  • customer_id
  • total_amount
  • percentile (1, 2, 3 или 4 - квартиль)

Q1 - нижние 25%, Q4 - верхние 25%.

Подсказка:

Используйте NTILE() или PERCENT_RANK().

Источник: типовая задача на оконные функции

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Решение

Это задача на разбиение клиентов по величине выручки с помощью оконных функций. Типичная сегментация для анализа VIP-клиентов.

Логика решения:

  1. Агрегируем заказы по клиентам — суммируем amount для каждого customer_id
  2. Применяем NTILE(4) — разбиваем на 4 равные группы по величине выручки
  3. Выводим результат — customer_id с квартилем

SQL запрос с NTILE:

WITH customer_totals AS (
  SELECT 
    customer_id,
    SUM(amount) as total_amount
  FROM orders
  GROUP BY customer_id
)
SELECT 
  customer_id,
  total_amount,
  NTILE(4) OVER (ORDER BY total_amount) as percentile
FROM customer_totals
ORDER BY total_amount DESC;

Альтернативный подход с PERCENT_RANK:

WITH customer_totals AS (
  SELECT 
    customer_id,
    SUM(amount) as total_amount
  FROM orders
  GROUP BY customer_id
)
SELECT 
  customer_id,
  total_amount,
  CASE 
    WHEN PERCENT_RANK() OVER (ORDER BY total_amount) <= 0.25 THEN 1
    WHEN PERCENT_RANK() OVER (ORDER BY total_amount) <= 0.50 THEN 2
    WHEN PERCENT_RANK() OVER (ORDER BY total_amount) <= 0.75 THEN 3
    ELSE 4
  END as percentile
FROM customer_totals
ORDER BY total_amount DESC;

Вариант с дополнительной информацией:

WITH customer_totals AS (
  SELECT 
    customer_id,
    SUM(amount) as total_amount,
    COUNT(*) as order_count,
    AVG(amount) as avg_order_amount
  FROM orders
  GROUP BY customer_id
),
quartiles AS (
  SELECT 
    customer_id,
    total_amount,
    order_count,
    avg_order_amount,
    NTILE(4) OVER (ORDER BY total_amount) as percentile,
    ROW_NUMBER() OVER (ORDER BY total_amount DESC) as rank_by_amount
  FROM customer_totals
)
SELECT 
  customer_id,
  total_amount,
  order_count,
  ROUND(avg_order_amount, 2) as avg_order_amount,
  percentile,
  CASE 
    WHEN percentile = 1 THEN 'Q1 (Bottom 25%)'
    WHEN percentile = 2 THEN 'Q2 (25-50%)'
    WHEN percentile = 3 THEN 'Q3 (50-75%)'
    WHEN percentile = 4 THEN 'Q4 (Top 25%)'
  END as segment
FROM quartiles
ORDER BY percentile DESC, total_amount DESC;

Разница между NTILE и PERCENT_RANK:

NTILE(4) — разбивает на 4 примерно равные по размеру группы. Если 100 клиентов, то каждая квартиль получит по 25 клиентов.

PERCENT_RANK() — возвращает относительную позицию (0.0-1.0). Используется для более точной сегментации.

Результат:

customer_id | total_amount | percentile | segment
100         | 50000        | 4          | Q4 (Top 25%)
101         | 45000        | 4          | Q4 (Top 25%)
102         | 20000        | 3          | Q3 (50-75%)
103         | 15000        | 2          | Q2 (25-50%)
104         | 5000         | 1          | Q1 (Bottom 25%)

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

  • WITH customer_totals — CTE для агрегации (обязательна, так как NTILE работает на развёрнутых данных)
  • NTILE(4) OVER (ORDER BY total_amount) — разбивает на 4 группы, упорядочивая по выручке
  • ORDER BY total_amount DESC — сортируем лучших клиентов первыми
  • Можно добавить дополнительные метрики (количество заказов, средний чек, дату последнего заказа)

Этот запрос используется для сегментации клиентов и определения стратегий удержания VIP-пользователей.

SQL: Процентиль выручки по клиентам | PrepBro