Category Revenue

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

Domain

SQL

Difficulty

medium

Seniority

L3

Problem

The finance team is assembling a category performance slide for the quarterly business review. For each product category they want the total revenue, the number of transactions behind it, and the average transaction size, leaving off any category whose total revenue comes in under $12,100. List the categories from highest revenue to lowest.

Which categories pull their weight?

Tables

products

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

transactions

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

Expected output

Worked solution and explanation

What this problem is really about

Underneath the slide costume this is a threshold on a grouped total. Anyone can total revenue per category; the tell is where the $12,100 cutoff lands. It compares each category's SUM(total_amount), a number that does not exist until the rows are grouped, so it cannot live in WHERE. Reference the aggregate there and the query errors. Test total_amount there instead and you are filtering single sales, none of which comes anywhere near $12,100, so the slide comes back empty. Forget the cutoff entirely and every category, the two-product Collectibles line included, lands in front of the executives.

Break down the requirements

Step 1: Join products to transactions

Join on product_id to attach category to each transaction. The category lives only on products, so every sale has to be fanned out to its category before anything can be totaled. Sales whose product_id is empty have no category to land in, and the inner join drops them.

Step 2: Aggregate three metrics

SUM(t.total_amount) for revenue, COUNT(*) for transaction count, and AVG(t.total_amount) for average transaction size, all grouped by p.category in a single pass.

Step 3: Filter on the total, then sort

HAVING SUM(t.total_amount) >= 12100 drops the categories that fall under the slide cutoff. This has to be HAVING, not WHERE, because the value being tested is the grouped total. Then ORDER BY total_revenue DESC puts the survivors highest first.

The solution

SELECT
    p.category,
    SUM(t.total_amount) AS total_revenue,
    COUNT(*) AS transaction_count,
    AVG(t.total_amount) AS avg_transaction_size
FROM products p
JOIN transactions t ON p.product_id = t.product_id
GROUP BY p.category
HAVING SUM(t.total_amount) >= 12100
ORDER BY total_revenue DESC

Common follow-up questions

• What if the cutoff was the median category revenue instead of a fixed dollar figure? (Tests computing the median of the category totals in a CTE and referencing it in `HAVING`.)
• How would you handle NULLs in `total_amount`? (`SUM` and `AVG` skip NULLs but `COUNT(*)` does not, so the average and the count would describe different rows.)
• What if you needed to add a percentage-of-total column? (Tests a grand total via `SUM(total_amount) OVER ()` computed over the grouped rows.)