✏️ Explanatory Question
Level: Hard — Core to customer segmentation, RFM analysis, and "top 10%" business questions.
customers| name | total_spent |
|---|---|
| Rumman | 5000 |
| Krushna | 4000 |
| Swetha | 3000 |
| Ritesh | 2000 |
| Manjula | 1500 |
| Elitam | 1000 |
| Sai | 800 |
| Lakshmi | 500 |
NTILE(n) distributes the rows into n approximately equal buckets, assigning each row a bucket number from 1 to n. If rows don't divide evenly, the earlier buckets get the extra rows.
PERCENT_RANK or CUME_DIST instead.
SELECT
name,
total_spent,
NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile,
CASE NTILE(4) OVER (ORDER BY total_spent DESC)
WHEN 1 THEN 'Platinum'
WHEN 2 THEN 'Gold'
WHEN 3 THEN 'Silver'
WHEN 4 THEN 'Bronze'
END AS tier
FROM customers;| name | total_spent | quartile | tier |
|---|---|---|---|
| Rumman | 5000 | 1 | Platinum |
| Krushna | 4000 | 1 | Platinum |
| Swetha | 3000 | 2 | Gold |
| Ritesh | 2000 | 2 | Gold |
| Manjula | 1500 | 3 | Silver |
| Elitam | 1000 | 3 | Silver |
| Sai | 800 | 4 | Bronze |
| Lakshmi | 500 | 4 | Bronze |
8 rows ÷ 4 buckets = 2 per tier. Evenly distributed.
-- PERCENT_RANK: relative standing from 0 (highest) as we order DESC
-- CUME_DIST: cumulative distribution (fraction of rows <= current)
SELECT
name,
total_spent,
ROUND(PERCENT_RANK() OVER (ORDER BY total_spent DESC), 3) AS pct_rank,
ROUND(CUME_DIST() OVER (ORDER BY total_spent DESC), 3) AS cume_dist
FROM customers;
-- Get the TOP 10% of spenders (highest CUME_DIST fraction from the top)
SELECT name, total_spent
FROM (
SELECT name, total_spent,
CUME_DIST() OVER (ORDER BY total_spent DESC) AS cd
FROM customers
) t
WHERE cd <= 0.10; -- top 10%| Function | Splits By | Best For |
|---|---|---|
| NTILE(n) | Equal row counts | Fixed buckets (quartiles, deciles) |
| PERCENT_RANK() | Relative rank (0–1) | "Better than X% of others" |
| CUME_DIST() | Cumulative fraction | "Top/bottom X%" cutoffs |
CUME_DIST/PERCENT_RANK cutoffs.
PARTITION BY region: NTILE(4) OVER (PARTITION BY region ORDER BY total_spent DESC). Each region gets its own independent 4 tiers — the partition restarts the bucketing per group.