# Top Five

> The five priciest items for the luxury section.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The editorial team is assembling a buyer's guide and needs the five most expensive products for the luxury highlight section. Show the product name, category, price, and whether the item is currently available. Products without a listed price should be excluded.

## Worked solution and explanation

### Why this problem exists in real interviews

Working with `products`, this problem tests filtering to the top rows after aggregation. The interviewer checks whether you choose the right window function and aggregate to the correct grain before ranking.

---

### Break down the requirements

#### Step 1: Aggregate per product_id

`GROUP BY product_id` with the appropriate aggregate function produces one summary row per group from the `products` table.

#### Step 2: Rank the results

`ORDER BY` the aggregate descending with `LIMIT` to surface the top entries.

---

### The solution

**Sort products by price descending and take the first five**

```sql
SELECT
    product_id,
    SUM(price) AS total_price
FROM products
GROUP BY product_id
ORDER BY total_price DESC
LIMIT 5
```

> **Cost Analysis**
>
> The GROUP BY reduces the 6K-row `products` table to the number of distinct `product_id` values. A covering index on `(product_id, price)` enables an index-only aggregate scan.

> **Interviewers Watch For**
>
> Interviewers verify you aggregate before sorting. Sorting raw rows gives per-row values, not group totals. The correct grain is one row per `product_id`.

> **Common Pitfall**
>
> Using the wrong aggregate function. `SUM` gives totals, `COUNT` gives volume, `AVG` gives rates. Read the prompt to determine which metric is needed.

---

## Common follow-up questions

- If two products share the same price at position 5, does LIMIT 5 include or exclude one of them? _(Tests that LIMIT truncates arbitrarily; FETCH FIRST 5 ROWS WITH TIES includes both.)_
- The prompt asks for in_stock status. If in_stock is NULL, should that product still appear? _(Tests NULL handling in projected columns; NULLs do not affect ORDER BY on price.)_
- If the table has fewer than 5 products, what does LIMIT 5 return? _(Tests understanding that LIMIT returns up to N rows, not exactly N.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/top_five)
- [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.