Top Product Categories by Sales

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain: SQL
Difficulty: easy
Seniority: L3
Asked in: Audible

Problem

Our category revenue dashboard stopped matching finance, so we want to rebuild it straight from the transactions and products tables. Total the transaction amounts for each product category, from highest revenue to lowest.

The highest-grossing categories.

Tables

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

products: 10 of 200 rows
product_id	product_name	category	price	rating	in_stock
1001	Basic Device 1X	Books	17.52	2.7	1
1050	Deluxe Bundle 2X	Clothing	25.05	4.4	1
1099	Pro Unit 3X	Home & Kitchen	32.58	2.1	1
1148	Ultra Tool 4X	Collectibles	40.11	3.8	1
1197	Essential Set 5X	Toys	47.64	1.5	1
1246	Classic System 6X	Beauty	0	3.2	1
1295	Smart Gadget 7X	Automotive	62.7	4.9	0
1344	Eco Kit 8X	Garden	70.23	2.6	1
1393	Turbo Pack 9X	Music	77.76	4.3	0
1442	Premium Widget 10X	Electronics	85.29	2	1

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

transactions: 10 of 200 rows
transaction_id	user_id	product_id	quantity	total_amount	transaction_date
1067	197	1001	2	23.46	2026-02-02
1134	294	1050	3	36.93	2026-03-03
1201	391	1099	4	50.4	2026-04-04
1268	488	1148	5	63.87	2026-05-05
1335	585	1197	0	0	2026-06-06
1402	682	1246	2	90.81	2026-07-07
1469	779	1295	3	104.28	2026-08-08
1536	876	1344	4	117.75	2026-09-09
1603	973	1393	5	-45	2026-01-10
1670	1070	1442	1	144.69	2026-02-11

Expected output

First 5 of 11 rows
category	total_revenue
Electronics	15016.8
Books	12501.39
Clothing	12487.92
Home & Kitchen	12222
Toys	12090.78

Worked solution and explanation

What this is really asking

category lives on products (20k rows), revenue lives on transactions (80M rows). Join on product_id, SUM total_amount per category, ORDER BY revenue DESC. The join direction matters at this row count.

Break down the requirements

Step 1: Get category onto each transaction

INNER JOIN transactions to products on product_id. A transaction with no matching product is corruption, not a category.

Step 2: Aggregate by category

SUM(t.total_amount) grouped by p.category. Use total_amount directly; promotions and refunds already settled into it.

Step 3: Sort descending

ORDER BY total_revenue DESC ranks all categories. No LIMIT.

REVENUE BY CATEGORY

SELECT
  p.category,
  SUM(t.total_amount) AS total_revenue
FROM products p
INNER JOIN transactions t
  ON p.product_id = t.product_id
GROUP BY p.category
ORDER BY total_revenue DESC

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

How would you include categories with zero sales? (LEFT JOIN with `products` on the left, wrap SUM in `COALESCE(..., 0)`.)
What if a product changes category over time? (Add SCD2 on `products` and join on a time-valid surrogate; current schema only carries latest.)
How would you add a percent-of-total revenue column? (Wrap in a CTE, divide each row by `SUM(total_revenue) OVER ()`.)