Brightlane's merchandising team needs both a sequential price ranking and a quartile assignment for every product in the catalog.
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.
Assumptions:
- The sequential position is
1for the lowest-priced product and increments by1for each subsequent product in price order. Every product receives a different sequential position; products with identicalpricevalues get consecutive positions in some order. - For the quartile column, products are sorted by
priceascending and assigned to one of four tiers based on position. Tier1covers the lowest-priced quarter; tier4covers the highest-priced quarter. When the row count does not divide evenly by4, the earlier tiers each receive one extra record. - The final result is sorted by
priceascending.
Output:
- One row per product, with columns
id,name,price,price_rank, 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,
ROW_NUMBER() OVER (
ORDER BY
price
) AS price_rank,
NTILE(4) OVER (
ORDER BY
price
) AS price_quartile
FROM
products
ORDER BY
priceThe shape
Two window functions on the same ORDER BY price sort, in one pass. ROW_NUMBER gives every product a unique sequential position from 1 to N; NTILE(4) cuts the same sorted sequence into four equal-row buckets. Reading them side by side exposes exactly how NTILE translates positions into tiers, including the uneven-division behavior.
Clause by clause
SELECT id, name, price, ROW_NUMBER() OVER (ORDER BY price) AS price_rank, NTILE(4) OVER (ORDER BY price) AS price_quartilereturns the product's identifying columns plus both window-function outputs. Both functions use the sameORDER BY priceinsideOVER, so they walk the catalog in the same sequence.ROW_NUMBERincrements by 1 for every row;NTILE(4)looks at the same position and translates it into a bucket from 1 to 4.FROM productsreads the full catalog. No filter.ORDER BY priceis the outer sort. It prints the result in ascending-price order, which is also the order both window functions used internally, so the printedprice_rankreads 1, 2, 3 down the page.
Why both window functions on the same query
The merchandising team needs both views of the catalog. The sequential rank lets them point at a specific product and say "this is the 17th cheapest"; the quartile lets them group products into tier-1-through-tier-4 segments. Computing them in one query against the same sort guarantees the two columns are internally consistent: the row at price_rank = 17 will always be the first row in its quartile if 17 happens to land on a bucket boundary, or sit somewhere in the middle of a quartile otherwise.
The trap
NTILE handles the uneven-division case by giving the earlier buckets one extra row each. With 64 products and 4 buckets, that is 16 per bucket exactly. With 65 products, bucket 1 would get 17 and the rest get 16; with 66, buckets 1 and 2 get 17 and buckets 3 and 4 get 16. The size difference is at most one row per bucket, but it is real and it changes where the boundaries fall. Looking at the price_rank and price_quartile columns together is the cleanest way to read the boundaries off the data: every price_quartile transition lines up with a specific price_rank value, and those transition points are not always at the same price value when ties straddle them.
You practiced two ordered-window functions in one query — ROW_NUMBER for a unique sequential position and NTILE for a bucket assignment, both ordered by the same expression.
