# Priciest Item in Each Category

> The most expensive item per category.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

For each product category, find the product with the top price. If there is a tie, include all tied products.

## Worked solution and explanation

### Why this problem exists in real interviews

This product analytics problem uses the `products` table to evaluate filtering and projection. Watch how the `category` and `price` columns interact in the grouping and filtering logic.

---

### Break down the requirements

#### Step 1: Rank products by price within category

`RANK() OVER (PARTITION BY category ORDER BY price DESC)` assigns rank 1 to the highest-priced product(s) in each category, with ties sharing rank 1.

#### Step 2: Filter to rank 1

Wrap in a subquery and `WHERE rnk = 1` to keep only the top-priced product(s) per category.

---

### The solution

**Rank products by price within category to find priciest item in eac...**

```sql
SELECT product_name, category, price
FROM (
    SELECT
        product_name,
        category,
        price,
        RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk
    FROM products
) ranked
WHERE rnk = 1
```

> **Cost Analysis**
>
> With `products` (15,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

- If `rating` in `products` is NULL for some rows, how would your aggregation or join logic be affected? _(Probes understanding of NULL propagation through joins and aggregate functions on `products.rating`.)_
- The `rating` 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.rating` 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/priciest_item_in_each_category)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.