Dense Rank Products by Price — Ranking Functions in SQL

The problem

Brightlane's product leaderboard assigns a tier number to products based on descending price.

Write a query to return the ID, name, and price of every product, plus the product's tier number by price in descending order.

Assumptions:

The products table has one row per product with an id, a name, and a price.
Tier number 1 goes to the highest-priced product, with tier values increasing as price decreases.
Products with the same price receive the same tier number. The next tier number after a tie is always exactly one higher, so tier values have no gaps.

Output:

One row per product, with columns id, name, price, and price_tier.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  name,
  price,
  DENSE_RANK() OVER (
    ORDER BY
      price DESC
  ) AS price_tier
FROM
  products

The shape

DENSE_RANK() OVER (ORDER BY price DESC) assigns each distinct price its own consecutive integer and gives every product at that price the same value. Two products tied at the top both get 1, the next-distinct price gets 2, with no gap in between regardless of how many products were tied at 1.

Clause by clause

SELECT id, name, price, DENSE_RANK() OVER (ORDER BY price DESC) AS price_tier returns each product's ID, name, price, and a tier number that groups products by price band. The window's ORDER BY price DESC defines the descending price order; without PARTITION BY, every product is in the same window.
FROM products reads the product catalog.

Why `DENSE_RANK` and not `RANK`

Both functions give tied rows the same number. RANK then skips ahead by the number of tied rows, so 1, 1, 3, .... DENSE_RANK does not skip, so 1, 1, 2, .... When the output is meant to read as "price tier" or "category level," consecutive integers without gaps map cleanly to tier 1, tier 2, tier 3. When the output is meant to be a competitive ranking where the gap communicates how many products sat above, RANK is the right function. The two are interchangeable on tie-free data and divergent the moment a tie appears.

You practiced DENSE_RANK() OVER (ORDER BY ...) — tied rows share the same value; the next value advances by exactly 1, so the sequence is gap-free.

Return the ID, name, and price of every product, plus the product's tier number by `price` in descending order

The shape

Clause by clause

Why `DENSE_RANK` and not `RANK`

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why DENSE_RANK and not RANK

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why `DENSE_RANK` and not `RANK`