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.

⌘/Ctrl + Enter to grade

Write a query, then grade it.

Hints

Try it first. Reveal a hint only when you are stuck.