BBampaa

Lessons › SQL › Deep Dive: Advanced Window Functions

SQL lesson

NTILE() for ranking into equal buckets

NTILE splits rows into a set number of equal-sized groups and stamps each row with a group number based on its position in the sorted order - no manual cutoffs needed.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Splitting rows into equal-sized buckets

SELECT name, total_spend, NTILE(3) OVER (ORDER BY total_spend) AS tier FROM customers

Result:

nametotal_spendtier
Sam Rivera320.751
Tomas Klein890.01
Jordan Lee1240.52
Riya Mehta2870.12
Min Chen3215.253
Alex Park4890.03

Step by step

  1. AS gives a column a friendlier name in the result.
    SELECT name, total_spend, NTILE(3) OVER (ORDER BY total_spend) AS tier
  2. FROM tells SQL which table to pull the data from.
    SELECT name, total_spend, NTILE(3) OVER (ORDER BY total_spend) AS tier
FROM customers;

Practice

Try each one in your head first, then open the answer.

1. Write a query on the orders table that splits rows into 10 equal-sized buckets based on revenue, using NTILE. Return the revenue column and a column called revenue_bucket with the bucket number.
SELECT revenue, NTILE(10) OVER (ORDER BY revenue) AS revenue_bucket FROM orders
2. Write a query on the store_sales table that splits rows into 4 equal-sized buckets based on revenue, using NTILE. Return the revenue column and a column called revenue_bucket with the bucket number.
SELECT revenue, NTILE(4) OVER (ORDER BY revenue) AS revenue_bucket FROM store_sales
3. Write a query on the products table that splits rows into 5 equal-sized buckets based on price, using NTILE. Return the price column and a column called price_bucket with the bucket number.
SELECT price, NTILE(5) OVER (ORDER BY price) AS price_bucket FROM products
4. Write a query on the employees table that splits rows into 5 equal-sized buckets based on salary, using NTILE. Return the salary column and a column called salary_bucket with the bucket number.
SELECT salary, NTILE(5) OVER (ORDER BY salary) AS salary_bucket FROM employees
5. Write a query on the reviews table that splits rows into 4 equal-sized buckets based on rating, using NTILE. Return the rating column and a column called rating_bucket with the bucket number.
SELECT rating, NTILE(4) OVER (ORDER BY rating) AS rating_bucket FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed