Разбить клиентов на децили по тратам через NTILE и вывести долю выручки каждого дециля
Таблица orders(customer_id, amount). Разбейте клиентов на 10 децилей по тратам, где дециль 1 содержит крупнейших плательщиков. По каждому децилю выведите его суммарную выручку и эту выручку как долю от общей.
-- назначить клиентам децили по тратам, затем выручку дециля и её долю от общейНапишите запрос.
NTILE(10) OVER (ORDER BY spend DESC) делит клиентов на 10 равных корзин по тратам. По децилю суммируют выручку и делят на итог SUM(SUM(spend)) OVER (). NTILE уравнивает число строк, не выручку.
- ✗Думать, что
NTILEуравнивает выручку корзины, а не число строк - ✗Ожидать, что
PERCENTILE_CONTвернёт метку корзины - ✗Пропускать
ORDER BY, оставляя корзиныNTILEв произвольном порядке
- →Почему верхний дециль может держать куда больше выручки, чем нижний?
- →Что происходит с размерами корзин, когда число строк не делится на 10?
Сначала считают траты по клиенту, затем NTILE(10) раскладывает их на 10 корзин равного размера, и группировка по децилю даёт выручку и её долю:
WITH customer_spend AS (
SELECT customer_id, SUM(amount) AS spend
FROM orders
GROUP BY customer_id
),
deciled AS (
SELECT customer_id, spend,
NTILE(10) OVER (ORDER BY spend DESC) AS decile
FROM customer_spend
)
SELECT decile,
SUM(spend) AS decile_revenue,
ROUND(100.0 * SUM(spend) / SUM(SUM(spend)) OVER (), 2) AS pct_of_revenue
FROM deciled
GROUP BY decile
ORDER BY decile;NTILE балансирует число клиентов в корзине, а не выручку, поэтому верхний дециль обычно держит намного больше 10% выручки. Без ORDER BY номера децилей были бы произвольными. SUM(SUM(spend)) OVER () берёт общий итог поверх сгруппированных строк.
