What does NTILE do, and how would you use it to label customers into quartiles by spend?

How to think about it

NTILE is SQL’s answer to pd.qcut with equal-count bins. The question is really probing whether you know the difference between equal-count bucketing (NTILE) and equal-width bucketing (a CASE with fixed thresholds) — and when each one is the right tool.

NTILE(n) orders the rows (within a partition, if defined) and stamps each with a bucket number from 1 to n. The buckets fill as evenly as possible; when the count doesn’t divide cleanly, the earliest buckets get the extra row. So 9 rows into NTILE(4) gives 3, 2, 2, 2 — not 2, 2, 2, 3.

A worked example — 9 customers into quartiles

Nine customers ordered by spend, split into four tiers, with a CASE translating the bucket number into a label:

SELECT customer_id, name, total_spend,
       NTILE(4) OVER (ORDER BY total_spend ASC) AS quartile,
       CASE NTILE(4) OVER (ORDER BY total_spend ASC)
         WHEN 1 THEN 'Bronze' WHEN 2 THEN 'Silver'
         WHEN 3 THEN 'Gold'   WHEN 4 THEN 'Platinum'
       END AS tier
FROM cust_spend
ORDER BY total_spend;

customer_id	name	total_spend	quartile	tier
8	Hana	310	1	Bronze
4	Dara	500	1	Bronze
6	Farah	750	1	Bronze
2	Bea	850	2	Silver
1	Aarav	1200	2	Silver
9	Iris	1650	3	Gold
5	Eli	2100	3	Gold
3	Chen	3400	4	Platinum
7	Gao	4800	4	Platinum

Bronze holds 3 customers, the rest hold 2 — that’s the “extra row goes to the earlier bucket” rule in action, since 9 doesn’t divide by 4. Bucket 1 is the lowest spenders, bucket 4 the highest. Add PARTITION BY region and the quartiles compute within each region, so a mid-spend customer in a frugal region can be Platinum while the same spend elsewhere is Bronze — useful for decile churn scoring too (NTILE(10) OVER (ORDER BY churn_score DESC)).