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 · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id,
name,
price,
NTILE(4) OVER (
ORDER BY
price
) AS price_quartile
FROM
products
ORDER BY
priceThe shape
NTILE(4) over an ascending price sort splits the catalog into four equal-row tiers and stamps each product with its tier number. The window function does the sort, the count, and the labelling in one pass. The outer ORDER BY price makes the printed result line up tier 1 first.
Clause by clause
SELECT id, name, price, NTILE(4) OVER (ORDER BY price) AS price_quartilereturns the four columns the merchandising team needs.NTILE(4)walks the products in ascendingpriceorder and assigns the first quarter of rows to bucket 1, the next quarter to bucket 2, and so on. TheORDER BY priceinsideOVERis what defines the sort the buckets are cut from.FROM productsreads the full catalog. There is noWHERE, so every product participates in the bucketing.ORDER BY priceis the outer sort. It controls the printed sequence so the result reads from cheapest to most expensive, which is also the order the tiers ascend in.
Why this and not ROW_NUMBER divided into groups
ROW_NUMBER would give you a sequential rank from 1 to N, and you would then have to derive a tier from that rank yourself. NTILE(4) is the purpose-built function for the question being asked: cut the sorted rows into four equal-row buckets. One function, one column, no arithmetic.
The trap
NTILE splits by row position, not by price gaps. Two products that share an identical price near a bucket boundary can land on opposite sides of that boundary. The reference result shows it directly: HDMI Cable 2m and Picture Frame 8x10 both cost 12.99 and both land in tier 1, but the same kind of tie at the 16th-to-17th row boundary would put two equal-priced rows in different tiers. The team should read price_quartile as "the bottom quarter of products by row count," not "every product below a hard price cutoff."
You practiced NTILE(4) OVER (ORDER BY ...) — distribute records into four position-based buckets; the assignment is by row-count, not by value range.
