Lesson 10.1
Top-N Reports in SQL
'Show me the top 5' is the most common report there is: aggregate, sort, then LIMIT.
Almost every dashboard starts with a Top-N question: the best-selling products, the busiest customers, the slowest orders. The recipe is always the same: aggregate to get a number per group,sort by it, then LIMIT to the few rows you care about.
Here "revenue" is sum(quantity * unit_price) across a product's line items. The starter already builds the full ranked list Your job is to keep only the top five.
Pattern
select category, sum(amount) as total
from sales
group by category
order by total desc
limit 10Schema · Garden ShopTable · order_items5 columns · 48 rows
Table · order_items
5 columns · 48 rowsOne row per line item within an order.
order_item_id intorder_id intproduct_id intquantity intunit_price decimal
Your task
Find the top 5 products by total revenue. Returnproduct_name and revenue, biggest first.
SQL Workbench
⌘↵ to run·
Expected answer
- Columns: product_name, revenue.
- Rows: the top 5 products by revenue.
- Revenue multiplies quantity by unit_price across every line item for that product.