The Lion's Share

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

mid

Asked in

Allstate

Problem

The merchandising finance team wants to see which product categories bring in the most money. Match every purchase to its product's category, then for each category report its total purchase revenue and how many purchases stood behind it, with the biggest earners listed first.

Every category claims its slice. Find out who really owns the table.

Tables

transactions

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

products

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

Expected output

Worked solution and explanation

What this really is

This is a fact-to-dimension join with a per-group rollup, dressed up as a merchandising review. The category a purchase belongs to lives only in products, never in transactions, so the first real move is the inner join that lends each purchase its category. After that it is a GROUP BY p.category with SUM(t.total_amount) for revenue and COUNT(*) for volume. The decision that separates a reproducible answer from a flaky one is the ORDER BY: rank by the summed revenue, whose per-category values are all distinct, not by the raw purchase count, where several categories tie on exactly 18 and come back in whatever order the engine feels like.

Break down the requirements

Step 1: Borrow the category from the dimension

transactions has no category column, so each purchase must be matched to its product with a join on product_id against products. An inner join is right here: a purchase with no matching product carries no category to report on, so it drops out rather than forming a null bucket.

Step 2: Collapse to one row per category

GROUP BY p.category folds every purchase in a category into a single output row. SUM(t.total_amount) gives the category's revenue and COUNT(*) gives how many purchases produced it. COUNT(*) is a raw row count, one per transaction, not a count of distinct users or products.

Step 3: Rank by revenue, and make it total

Order by total_revenue descending so the biggest earners surface first. The reason this beats the intuitive COUNT(*) ordering is determinism: several categories tie at exactly 18 purchases, so ranking by the count leaves those rows in engine-arbitrary order, while the per-category revenue sums are all distinct and give one reproducible sequence. Note you order by the SUM, not by a raw total_amount column, which no longer exists after the grouping.

The solution

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

Common follow-up questions

• How would you change the query to also include categories that exist in the catalog but have never been purchased, showing them with zero revenue? (Tests switching to a left join from products to transactions and coalescing the NULL sum to zero.)
• The team now wants each category's percentage of total revenue alongside the totals. How would you add that in one pass? (Tests computing a part-to-whole share in a single pass with an aggregate-of-an-aggregate window, `SUM(SUM(total_amount)) OVER ()`, rather than a second scan of the table.)