Level 8 · Customer spend quartiles

NTILE(n) splits ordered rows into n groups of nearly equal size and returns the group number. It is the quickest way to build quartiles (4), quintiles (5) or deciles (10).

Recap: equal-sized groups

SELECT product_id, name, unit_price,
       NTILE(4) OVER (ORDER BY unit_price DESC, product_id) AS price_quartile
FROM products;

The 120 products divide evenly, so each quartile holds 30. When the count does not divide evenly, the first groups get one extra row: the 1,730 customers with a completed order split into 433, 433, 432 and 432.

NTILE cuts by row count, not by value. Two customers with nearly the same revenue can land in different quartiles if the boundary falls between them, and the quartiles cover very different spend ranges. That is the point of the ticket’s min and max columns:

spend_quartile	customers	min_revenue	max_revenue	pct_of_revenue
1	433	3,067.95	184,730.27	78.6
2	433	1,182.90	3,054.89	14.2
3	432	454.97	1,182.74	5.7
4	432	8.79	453.95	1.4

The top quarter of customers brings almost four fifths of revenue, and its range is enormous: three business accounts have each spent more than $100,000, while the quarter starts at about $3,000. The bottom quarter, 432 customers, adds up to 1.4% of revenue, which is useful context before anyone spends money on reactivating them.

Grouping by a window result

The window runs in a CTE first; only then can you GROUP BY spend_quartile, because a window result cannot be grouped in the same SELECT that computes it. The share uses a window on top of the grouped sums, SUM(SUM(revenue)) OVER (), which you have seen with counts.

Common mistakes

• Ascending order. ORDER BY revenue makes quartile 1 the smallest spenders. State the direction.
• No tie-breaker. Equal revenues at a boundary can switch groups between runs.
• Confusing NTILE with value bands. If finance wants fixed thresholds ($500, $2,000), use CASE instead.

In SQL Server

NTILE(4) OVER (ORDER BY revenue DESC, customer_id) is identical. SQL Server’s PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY revenue) OVER () gives the value at the boundary instead of a group number.