## Контекст (таблицы и пример данных) ### Таблицы - `categories(id, name)` - `products(id, category
SQL Developer
Senior
## Контекст (таблицы и пример данных)
### Таблицы
- `categories(id, name)`
- `products(id, category_id, name, price)`
- `order_items(order_id, product_id, qty, unit_price)`
### Пример данных
**categories**
| id | name |
|---:|----------|
| 1 | Phones |
| 2 | Laptops |
**products**
| id | category_id | name | price |
|---:|------------:|--------------|------:|
| 10 | 1 | Phone A | 500 |
| 11 | 1 | Phone B | 700 |
| 20 | 2 | Laptop X | 1200 |
**order_items**
| order_id | product_id | qty | unit_price |
|---------:|-----------:|----:|-----------:|
| 100 | 10 | 2 | 480 |
| 101 | 11 | 1 | 700 |
| 102 | 20 | 1 | 1200 |
---
## Задача
Найти **топ-1 товар по выручке** в каждой категории.
Выручка = `SUM(qty * unit_price)`.
Вернуть: `category_name`, `product_name`, `revenue`.
Ответы
```sql
WITH rev AS (
SELECT
c.name AS category_name,
p.name AS product_name,
SUM(oi.qty * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN categories c ON c.id = p.category_id
GROUP BY c.name, p.name
)
SELECT category_name, product_name, revenue
FROM (
SELECT r.*, ROW_NUMBER() OVER (PARTITION BY category_name ORDER BY revenue DESC, product_name) AS rn
FROM rev r
) t
WHERE rn = 1;
```