Q: Highest-Grossing Items
"You have an orders table with item_id, quantity, and price. Write a query to find the top 5 items that generated the most total revenue."

Step by Step Answer:

You are given an orders table with three important columns:

item_id
quantity
price

You need to:

Calculate the total revenue for each item.
(Revenue = quantity × price)
Find the top 5 items that earned the most revenue.

Sample Data (`orders`):

item_id	quantity	price
101	2	50
102	1	100
101	1	50
103	5	20
104	3	70
105	2	40
102	1	100

Step-by-Step Explanation:

Step 1: Calculate Revenue for Each Order

For every row, revenue is quantity × price.

For example:

First row: 2 × 50 = 100
Second row: 1 × 100 = 100
Third row: 1 × 50 = 50
and so on...

But this only gives revenue per order, not per item yet.

Step 2: Add Up Revenue for Each Item

We need to group by item_id and sum the revenue across all orders.

Example:

Item 101: (2×50) + (1×50) = 100 + 50 = 150
Item 102: (1×100) + (1×100) = 100 + 100 = 200
Item 103: (5×20) = 100
Item 104: (3×70) = 210
Item 105: (2×40) = 80

Step 3: Find Top 5 Items by Revenue

Now sort the items by their total revenue in descending order and pick the top 5.

In our example, sorted by revenue:

Item 104 → 210
Item 102 → 200
Item 101 → 150
Item 103 → 100
Item 105 → 80

Top 5 items are 104, 102, 101, 103, 105.

SQL Query:

SELECT 
  item_id,
  SUM(quantity * price) AS total_revenue
FROM 
  orders
GROUP BY 
  item_id
ORDER BY 
  total_revenue DESC
LIMIT 5;

Explanation of SQL:

SUM(quantity * price) calculates total revenue for each item.
GROUP BY item_id groups all orders of the same item together.
ORDER BY total_revenue DESC sorts the items so that the highest revenue is first.
LIMIT 5 keeps only the top 5 items.