Top Products per Category

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

L4

Asked in

Visa

Problem

Each row of the transactions table is one sale with the revenue it brought in, and the products table puts every product in a category. List the top five products by total sales revenue in each category, categories alphabetically and the biggest sellers first within each.

Five winners per category.

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

This is a per-group cutoff dressed up as a merchandising report. Anyone can total revenue per product; what separates candidates is whether 'top five' is measured inside each category or across the whole catalog. Write ORDER BY total_sales DESC LIMIT 5 and you get five rows for the entire store: on this seed that is one product apiece from 'Electronics', 'Garden', 'Automotive', 'Beauty' and 'Toys', and the other six categories, the two-product 'Collectibles' shelf included, silently disappear.

Total first, then cut inside each category

Step 1: Collapse sales to one row per product

Inner-join transactions to products on product_id and take SUM(total_amount) per category and product_name. total_amount is already the revenue of the sale, so never multiply by quantity again; the voided sale at 0 and the refund at -45 simply pull their product's total down. Sales whose product_id is NULL match nothing and drop out, which is what a per-product report wants.

Step 2: Number the products inside each category

Number each product within its category, highest total_sales first, with product_name behind it so two equal totals could never fight over the fifth slot. The count restarts at 1 for every category, and that restart is the whole problem.

Step 3: Cut at five one level up, then sort

Keep sales_rank <= 5 in an outer query: the number does not exist until the window has run, so it cannot be filtered in the SELECT that computes it. Sort by category, then total_sales descending. 'Collectibles' holds only two products and keeps both: a cutoff never pads.

WITH product_sales AS (
    SELECT p.category, p.product_name, SUM(t.total_amount) AS total_sales
    FROM transactions t
    JOIN products p ON t.product_id = p.product_id
    GROUP BY p.category, p.product_name
),
ranked AS (
    SELECT category, product_name, total_sales,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC, product_name) AS sales_rank
    FROM product_sales
)
SELECT category, product_name, total_sales
FROM ranked
WHERE sales_rank <= 5
ORDER BY category, total_sales DESC

One cutoff for the store

ORDER BY total_sales DESC LIMIT 5 keeps the five biggest products overall: one each from 'Electronics', 'Garden', 'Automotive', 'Beauty' and 'Toys', and nothing at all from the other six categories.

One cutoff per category

ROW_NUMBER() with PARTITION BY category restarts the count on every shelf, so each category contributes up to five rows: 52 in all, because 'Collectibles' only has two to give.

Common follow-up questions

• What changes if a category should show every product tied with its fifth-place product? (Swapping `ROW_NUMBER` for `RANK` or `DENSE_RANK` and saying what each does at the cutoff.)
• How would you restrict the report to a single year of sales? (Filtering `transaction_date` before the aggregate, never after the cut.)
• What if two products in different categories, or the same one, share a `product_name`? (Grain: totaling by `product_id` and carrying the name along, and when the difference matters.)