NTILE and Bucketing

Concepts covered: Bucketing

NTILE(n) sorts the partition, cuts it into n groups of as equal a size as the row count allows, and labels each row with its group number from 1 to n. The numbering starts at the top of the order, so NTILE(4) OVER (ORDER BY total_spent DESC) puts the biggest spenders in bucket 1. The function answers a question RANK cannot: a rank says a customer is 6th, a quartile says they are in the bottom quarter, and that statement holds whether the table has 8 customers or 8 million.

SELECT
  customer_name,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
FROM customer_summary

The 8 rows split into 4 buckets of 2. The engine orders them Dev 2500, Ann 1800, Finn 1200, Cara 960, Gia 700, Ben 420, Hal 310, Eve 150, then deals the buckets out in pairs from the top, so Dev and Ann take bucket 1 and Hal and Eve take bucket 4. A bucket depends on position alone, never on the amount. Ben at 420 and Hal at 310 are only 110 apart but land in different quartiles, while Dev and Ann are 700 apart and share one.

When the rows do not divide evenly

Splitting 8 rows into 3 buckets leaves 2 left over, and the rule is that the leftovers go to the lowest-numbered buckets, 1 row each. NTILE(3) therefore gives buckets 1 and 2 an extra row apiece, for 3 rows each, and leaves 2 rows for bucket 3. Bucket sizes never differ by more than 1 row, and the larger buckets always come first.

SELECT
  customer_name,
  total_spent,
  NTILE(3) OVER (ORDER BY total_spent DESC) AS spending_tier
FROM customer_summary

NTILE never gives 2 rows the same bucket on account of a tie. If Ben and Hal both had 420, one would still take the last slot of bucket 2 and the other would drop to bucket 3, in an order the engine picks. Add a tie-breaker to the ORDER BY, such as customer_name, so the split comes out the same on every run.

NTILE buckets

• Bucket sizes differ by at most 1 row
• The cutoffs move as the data changes
• A top bucket always exists, even when every customer is small
• Hard to explain as a figure: there's no fixed dollar line

CASE on fixed thresholds

• Bucket sizes vary with the data
• The cutoffs stay where the business set them
• A bucket can be empty when nobody crosses its line
• Easy to explain: spent over 1000 means tier 1

Bucketing inside partitions

NTILE(4) OVER (PARTITION BY region ORDER BY total_spent DESC) deals the quartiles out within each region. A customer with 700 could be quartile 1 in a region of 4 small accounts and quartile 3 in a region of 40 large ones, which is the behaviour you want when each region's team is judged against its own customers. Drop the partition when the comparison is meant to be global.

A table has 10 rows. What bucket sizes does NTILE(4) OVER (ORDER BY score DESC) produce, from bucket 1 to bucket 4?

• 3, 3, 2, 2, the 2 leftover rows going to the first buckets
• 2, 2, 3, 3, the 2 leftover rows going to the last buckets
• 4, 2, 2, 2, every leftover row landing in bucket 1
• 2, 2, 2, 2, with the 2 extra rows left unbucketed

About this lesson

This section is part of the Window Functions: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.