Category Sales Summary

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

Workday

Problem

A merchandising team is sizing up how each product category performs, looking only at items currently in stock. For every category with a sale, show how many different products it sold and the total revenue they brought in, highest revenue first.

Category by category. How did they do?

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 distinct-product count wearing a sales-report costume. The skill being probed: can you count PRODUCTS, not sales, per category while summing money in the same pass and keeping discontinued lines out? Anyone can join transactions to products and group by category. The trap is the product figure: a popular product gets bought over and over, so a plain COUNT of the joined rows counts that product once per receipt. Reach for COUNT(*) and your 'products' number is really a sales number, and the categories carrying a few hot repeat-sellers balloon up the leaderboard for no real reason. Miss the in-stock filter and you also fold discontinued items' revenue back into the totals.

Working it out

Step 1: Join, then drop out-of-stock lines

Join transactions to products on product_id so every sale carries its category, then keep only in-stock items with p.in_stock = 1. That filter matters more than it looks: out-of-stock products still have historical transactions, so with no filter you quietly add discontinued lines back into both the product count and the revenue.

Step 2: Dedup the products, total the money

Group by p.category. Use COUNT(DISTINCT t.product_id) for products so a hot seller bought many times still counts as one product, and SUM(t.total_amount) for revenue so you total the amount actually charged rather than recomputing price times quantity.

Step 3: Sort by revenue, let the join filter

ORDER BY total_revenue DESC floats the strongest categories to the top, which is the only order the ask names. There is no HAVING to write: an inner join means a category with no sale never appears, which is exactly the 'made a sale' rule.

The solution

SELECT
    p.category,
    COUNT(DISTINCT t.product_id) AS products_sold,
    SUM(t.total_amount) AS total_revenue
FROM transactions t
JOIN products p ON t.product_id = p.product_id
WHERE p.in_stock = 1
GROUP BY p.category
ORDER BY total_revenue DESC

Common follow-up questions

• How would you also list categories that sold nothing, showing zero products and zero revenue? (Forces LEFT JOIN from products grouped by category with COALESCE on the aggregates.)
• If a category's top product just went out of stock, what happens to its revenue rank here? (Probes whether they see that revenue and product counts move independently under the in-stock filter.)
• How would you also report the number of different customers per category alongside the product count? (Tests whether they can add a second dedup dimension without double-counting.)