medium💻 Coding
Write a SQL query to find the top 3 SKUs by revenue per region.
Given the following schema:
```sql
orders (order_id, sku_id, region, quantity, unit_price, order_date)
```
Write a SQL query that returns the **top 3 SKUs by total revenue for each region**, for the last 30 days.
Output columns: `region`, `sku_id`, `total_revenue`, `rank`
💡 Hints (2)
- 1.Window functions: ROW_NUMBER() or RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC)
- 2.Filter by order_date in a CTE first, then rank.
✅ View Solution
```sql
WITH revenue AS (
SELECT
region,
sku_id,
SUM(quantity * unit_price) AS total_revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY region, sku_id
),
ranked AS (
SELECT
region,
sku_id,
total_revenue,
RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC) AS rank
FROM revenue
)
SELECT region, sku_id, total_revenue, rank
FROM ranked
WHERE rank <= 3
ORDER BY region, rank;
```