Split customers into spend deciles with NTILE and report each decile's revenue share
Table orders(customer_id, amount). Rank customers into 10 spend deciles, where decile 1 holds the highest spenders. Per decile, report its total revenue and that revenue as a share of overall revenue.
-- assign customers to spend deciles, then each decile's revenue and share of totalWrite the query.
NTILE(10) OVER (ORDER BY spend DESC) splits customers into 10 equal buckets by spend. Group by decile, sum revenue, divide by the total from SUM(SUM(spend)) OVER (). NTILE equalizes count, not revenue.
- ✗Thinking
NTILEequalizes revenue per bucket instead of row count - ✗Expecting
PERCENTILE_CONTto return a bucket label - ✗Omitting
ORDER BY, leavingNTILEbuckets in arbitrary order
- →Why can the top decile hold far more revenue than the bottom one?
- →What happens to bucket sizes when the row count isn't divisible by 10?
First total spend per customer, then NTILE(10) lays customers into 10 equal-sized buckets, and grouping by decile gives the revenue and its share:
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 balances the customer count per bucket, not revenue, so the top decile usually holds well over 10% of revenue. Without ORDER BY the decile numbers would be arbitrary. SUM(SUM(spend)) OVER () takes the grand total across the grouped rows.
