Cohort Segmentation Using NTILE() Quartiles in SQL

Question

Given `customer_spend` (customer_id, annual_spend), divide customers into 4 equal quartiles using `NTILE(4)`. Assign 'Top 25% (Q4)' to tier 4.

Accepted Answer

Optimal Solution:
SELECT 
  customer_id,
  annual_spend,
  NTILE(4) OVER (ORDER BY annual_spend ASC) AS spend_quartile
FROM customer_spend
ORDER BY annual_spend DESC;

Technical Explanation:
`NTILE(4)` divides ordered records into 4 roughly equal buckets, assigning bucket numbers 1 through 4.

customer_id	annual_spend
1	50000
2	35000
3	20000
4	5000

customer_id	annual_spend	spend_quartile
4	5000	1
3	20000	2
2	35000	3
1	50000	4