Practice · hard · dataset: ecom
Top products per category
Merchandising wants a category page that leads with its best sellers. For each product category, return the three products that generated the most revenue.
Revenue for a product is the sum of quantity * unit_price over every line item that references it,
regardless of the order’s status.
Return category, product_id, name, and revenue, with revenue rounded to two decimals. Sort by
category ascending and, within a category, from the highest revenue to the lowest. Break revenue
ties by product_id ascending. Return at most three products per category; a category with fewer
than three products returns all of them.
Tables available
| Table | Columns |
|---|---|
products |
product_id, name, category, unit_price |
order_items |
order_id, product_id, quantity, unit_price |
Use the line item’s unit_price, not the product’s current price: the sale price is what the
customer actually paid.
Write a query, then grade it.
Hints
Try it first. Reveal a hint only when you are stuck.