NTILE and Percentile Functions in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this ROW_NUMBER, RANK, DENSE_RANK
What are NTILE and Percentiles in SQL?
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.
SELECT name, price, NTILE(4) OVER (ORDER BY price) AS quartile FROM products ORDER BY price
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.
Can two equal values land in different NTILE buckets?
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.
How do you compute a median or p90 in PostgreSQL?
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.
Should you use NTILE or a percentile function?
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.
Practice NTILE and Percentiles in SQL
Brightlane's merchandising team is segmenting the product catalog into four price tiers for promotional planning.
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.
Assumptions:
- Products are sorted by
priceascending and assigned to one of four tiers based on position. Tier1covers the lowest-priced quarter of products by row count; tier4covers the highest-priced quarter. - When the row count does not divide evenly by
4, the earlier tiers each receive one extra record. - Two products with identical
pricevalues may land in different tiers if they fall on opposite sides of a tier boundary. - The final result is sorted by
priceascending.
Output:
- One row per product, with columns
id,name,price, andprice_quartile. Sorted bypriceascending.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 NTILE and Percentiles 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.
Start learning to practice all 9 NTILE and Percentiles problems, with instant grading and mastery tracking.
Common questions about NTILE and Percentiles
What does NTILE do when the rows do not divide evenly?
The earlier buckets take the extra rows. Five rows into three buckets gives two, two and one rather than raising an error or leaving a bucket short at the front. The buckets are as equal as the row count allows.
What is the difference between PERCENTILE_CONT and PERCENTILE_DISC?
One interpolates and one does not. Across two values of ten and twenty, the continuous form returns fifteen, which is in your data nowhere, while the discrete form returns ten, which is a real row. Pick the discrete form when the answer has to be a value that exists.
Does NTILE need an ORDER BY inside OVER?
It runs without one, and the buckets are then meaningless because nothing decides which rows are low and which are high. Any use of NTILE that is about ranking needs the ordering, even though leaving it out raises no complaint.
