NTILE and Percentile Functions in SQL
NTILE divides a partition's rows into a specified number of ranked buckets and assigns each row a bucket number. The percentile functions PERCENTILE_CONT and PERCENTILE_DISC compute a value at a specified percentile position within a sorted set of values. These are separate mechanisms with separate purposes, but both answer questions about where a value sits in a distribution.
Before this ROW_NUMBER, RANK, DENSE_RANK
NTILE and the percentile functions both answer questions about where values sit in a distribution — but they answer different questions and work in completely different ways.
NTILE divides rows into a fixed number of buckets based on their sort order. Pass it the number of buckets you want, and it assigns each row a bucket number: 1 for the first group, 2 for the next, and so on. The result stays row-level: every row keeps its data and gets a new bucket label. Your manager wants products grouped into four price tiers? That's NTILE.
Products in bucket 1 are the lowest-priced by row count, bucket 4 the most expensive. If the product count doesn't divide evenly, the earlier buckets get one extra row each.
The one thing that trips people up with NTILE
NTILE splits rows by position, not by value gaps. Two products with identical prices can land in different buckets if they straddle a bucket boundary — NTILE has to put them somewhere, and position is all it has to go on.
This means "bucket 1" doesn't guarantee every product in it has a lower price than every product in bucket 2. It guarantees the bottom quarter by row count is in bucket 1. For most segmentation work that's fine. For precise value-threshold analysis, use the percentile functions instead.
PERCENTILE_CONT and PERCENTILE_DISC
These don't label rows — they return the value at a specific percentile threshold. Pass a fraction between 0 and 1, and they return the value at that point in the sorted distribution.
SELECT
region,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) AS median_revenue,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY revenue) AS median_revenue_disc
FROM orders
GROUP BY regionThe WITHIN GROUP (ORDER BY ...) syntax is specific to these functions — it's not the same as ORDER BY inside OVER. This is a grouped aggregate that produces one row per region.
The difference between CONT and DISC: PERCENTILE_DISC always returns an actual value from the data — it picks the row at or just above the requested percentile. PERCENTILE_CONT interpolates — if the 50th percentile falls between two rows, it returns a weighted average of the two surrounding values, which may not exist in your data.
For the median (0.5), PERCENTILE_CONT on an even-row dataset will return the average of the two middle values. PERCENTILE_DISC will return the lower of the two.
Choosing between them
NTILE answers: which bucket does this row belong to? It keeps all your rows and adds a label.
PERCENTILE_CONT and PERCENTILE_DISC answer: what value sits at this threshold? They collapse rows into one number per group.
For tagging customers into tiers, use NTILE. For computing the median, p90, or p99 of a metric by segment, use the percentile functions.
9 NTILE and Percentile Functions practice problems
Write a query to return every product's ID, name, price, and the product's price tier across the catalog. Sort the final result by `price` ascending.
Write a query to return every order's ID, customer ID, total amount, and the order's value tier across the full order set. Sort the final result by `total_amount` ascending.
Write a query to return every order's ID, status, total amount, and the order's spend tier within its status group. Sort the final result by `status` ascending, then `total_amount` ascending.
Write a query to return the interpolated median `total_amount` across every order as a single row.
Write a query to return the order status, the interpolated median `total_amount`, and the actual-value median `total_amount` for each `status`. Sort the final result by `status` ascending.
Write a query to return the 25th-percentile, 50th-percentile, and 75th-percentile actual-value salary across every current pay record as a single row.
Write a query to return every delivered order's ID, customer ID, total amount, and the order's quintile across delivered orders. Sort the final result by `total_amount` descending.
Write a query to return every product's ID, name, price, the product's sequential position in the price ordering, and the product's price quartile. Sort the final result by `price` ascending.
Write a query to return the order status, the total order count, the interpolated median `total_amount`, and the interpolated 90th-percentile `total_amount` for each `status`. Sort the final result by `status` ascending.
These problems are part of the NTILE and Percentile Functions lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.