Schema and sample data
| product_id | category | price |
|---|---|---|
| 1 | Electronics | 500.00 |
| 2 | Electronics | 200.00 |
| 3 | Books | 20.00 |
| 4 | Books | 15.00 |
| 5 | Clothing | 40.00 |
| 6 | Clothing | 60.00 |
| order_item_id | product_id | quantity |
|---|---|---|
| 1 | 1 | 3 |
| 2 | 2 | 2 |
| 3 | 3 | 10 |
| 4 | 4 | 5 |
| 5 | 5 | 8 |
| 6 | 6 | 4 |
| 7 | 1 | 1 |
Show setup SQL (copy to run)
CREATE TABLE Products (
product_id INT PRIMARY KEY,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
CREATE TABLE OrderItems (
order_item_id INT PRIMARY KEY,
product_id INT NOT NULL,
quantity INT NOT NULL
);
INSERT INTO Products (product_id, category, price) VALUES
(1, 'Electronics', 500.00),
(2, 'Electronics', 200.00),
(3, 'Books', 20.00),
(4, 'Books', 15.00),
(5, 'Clothing', 40.00),
(6, 'Clothing', 60.00);
INSERT INTO OrderItems (order_item_id, product_id, quantity) VALUES
(1, 1, 3),
(2, 2, 2),
(3, 3, 10),
(4, 4, 5),
(5, 5, 8),
(6, 6, 4),
(7, 1, 1);You have a Products table and an OrderItems table. Total revenue for a category is SUM(price * quantity) across every order line for products in that category. Return the single category with the highest total revenue, along with that revenue. If there were a tie for the top, returning all tied categories is acceptable.
Expected result
| category | revenue |
|---|---|
| Electronics | 2400.00 |
Show hint
Join OrderItems to Products so each order line knows its category and price, then GROUP BY category and total up SUM(price * quantity). To pick just the winner, sort by that total descending and keep the top row.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT TOP 1 WITH TIES
p.category,
SUM(p.price * oi.quantity) AS revenue
FROM Products p
JOIN OrderItems oi ON oi.product_id = p.product_id
GROUP BY p.category
ORDER BY revenue DESC;Each order line lives in OrderItems but the price and category live in Products, so the first step is a join on product_id. After the join, every line carries its price and category, and price * quantity gives the revenue for that line. Grouping by category and taking SUM of that product rolls the lines up into one total per category.
Working the sample data: Electronics = 500*3 + 200*2 + 500*1 = 1500 + 400 + 500 = 2400; Books = 20*10 + 15*5 = 200 + 75 = 275; Clothing = 40*8 + 60*4 = 320 + 240 = 560. Electronics wins at 2400.00. ORDER BY revenue DESC sorts the totals highest first, and TOP 1 keeps only the leader. Using TOP 1 WITH TIES is a small safety net: if two categories were tied for the highest revenue it returns both instead of arbitrarily dropping one.
A common mistake is to sum price without multiplying by quantity, which counts a 10-unit line the same as a 1-unit line. Another is joining in the wrong direction and losing categories that have orders. If you prefer a subquery, you can compute the per-category totals first and then keep only the ones equal to the maximum: ... GROUP BY category HAVING SUM(price * quantity) = (SELECT MAX(t.rev) FROM (SELECT SUM(price * quantity) AS rev FROM Products JOIN OrderItems ...) t). That HAVING = MAX form naturally returns every tied category and mirrors what WITH TIES does here.