SQL NTILE
Splits ordered rows into a fixed number of roughly equal-sized buckets.
What & Why
NTILE(n) numbers rows from 1 through n after ordering them inside a window. It creates quartiles, deciles, and other relative segments when ranked groups are more useful than fixed thresholds.
See How It Works
BUSINESS QUESTION
Marketing divides campaigns into four spend tiers from highest to lowest.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
EXAMPLE QUERY
SELECT
id AS campaign_id,
name,
spend,
NTILE(4) OVER (ORDER BY spend DESC, id) AS spend_quartile
FROM marketing.campaigns
ORDER BY spend_quartile, spend DESC, campaign_id;RESULT — four campaigns split into quartiles
| campaign_id | name | spend | spend_quartile |
|---|---|---|---|
| 4 | Enterprise Search | 60000.00 | 1 |
| 1 | Spring Launch | 55000.00 | 2 |
| 3 | Finance Retargeting | 50000.00 | 3 |
| 2 | Retention Webinar | 45000.00 | 4 |
With four input rows and four buckets, each campaign occupies one quartile.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario