# Price Rank

> In every category, someone charges the most. Who's on top?

Canonical URL: <https://datadriven.io/problems/price_rank>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The catalog team wants competitive price positioning inside each category. Within each category, rank products by price with the most expensive at position 1, and let ties share a position. Skip rows where price is NULL. Return the product_name, category, price, and its position.

## Tables

### products

- product_id INTEGER
- product_name TEXT
- category TEXT
- price REAL
- rating REAL
- in_stock INTEGER

Sample rows (10 of 200):

| product_id | product_name | category | price | rating | in_stock |
| --- | --- | --- | --- | --- | --- |
| 1001 | Basic Device 1X | Books | 17.52 | 2.7 | 1 |
| 1050 | Deluxe Bundle 2X | Clothing | 25.05 | 4.4 | 1 |
| 1099 | Pro Unit 3X | Home & Kitchen | 32.58 | 2.1 | 1 |
| 1148 | Ultra Tool 4X | Collectibles | 40.11 | 3.8 | 1 |
| 1197 | Essential Set 5X | Toys | 47.64 | 1.5 | 1 |
| 1246 | Classic System 6X | Beauty | 0 | 3.2 | 1 |
| 1295 | Smart Gadget 7X | Automotive | 62.7 | 4.9 | 0 |
| 1344 | Eco Kit 8X | Garden | 70.23 | 2.6 | 1 |
| 1393 | Turbo Pack 9X | Music | 77.76 | 4.3 | 0 |
| 1442 | Premium Widget 10X | Electronics | 85.29 | 2 | 1 |

## Expected output

First 5 of 191 rows:

| product_name | category | price | position |
| --- | --- | --- | --- |
| Eco Widget v97 | Automotive | 1831.91 | 1 |
| Eco Widget v87 | Automotive | 1739.21 | 2 |
| Eco Widget v77 | Automotive | 1646.51 | 3 |
| Eco Widget v67 | Automotive | 1553.81 | 4 |
| Eco Widget v57 | Automotive | 1461.11 | 5 |

## Worked solution and explanation

### Why this problem exists in real interviews

The core skill being tested is `DENSE_RANK` for gapless ranking, applied to the `products` table in a product analytics context. Getting columns like `price`, `rating`, and `category` right is where most candidates slip.

---

### Break down the requirements

#### Step 1: Filter out NULL prices

`WHERE price IS NOT NULL` ensures NULL ratings do not receive a rank.

#### Step 2: Rank within each category

`DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC)` assigns positions with ties sharing a rank and no gaps.

#### Step 3: Return the required columns

Select `product_name`, `category`, `price`, and the rank.

---

### The solution

**Dense-rank for price rank**

```sql
SELECT product_name, category, price, rnk AS position
FROM (
    SELECT
        product_name,
        category,
        price,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk
    FROM products
    WHERE price IS NOT NULL
) ranked
```

> **Cost Analysis**
>
> With `products` (18,000 rows), this query scans a small dataset. No indexing is needed for this volume. At production scale, an index on the primary filter column would improve performance.

> **Interviewers Watch For**
>
> Interviewers evaluate whether you translate the English requirements into the correct SQL clauses on the first attempt. They watch for clean syntax, correct column references, and whether you verify edge cases before declaring the query complete.

> **Common Pitfall**
>
> The most common mistake is misreading the prompt's filtering or grouping requirements. Double-check which columns to group by, which to aggregate, and whether the output should be filtered with `WHERE` (before grouping) or `HAVING` (after grouping).

---

## Common follow-up questions

- The `rating` column in `products` has a 5% null rate. How does your query handle rows where `rating` is NULL, and could that silently change the result count? _(Tests whether the candidate accounts for NULLs in `products.rating` and understands how aggregates skip NULL values.)_
- The `category` column in `products` is heavily skewed toward a few popular values. How would data skew affect parallel execution of your query? _(Tests understanding of skew in `products.category` and its impact on distributed query performance.)_
- What is the default window frame for your window function, and would explicitly setting ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW change anything? _(Tests knowledge of implicit vs explicit window frame specifications.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/price_rank)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.