Best in Class

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

Barclays

Problem

Across every sale on record, a merchandising analyst wants each product category's top earner: the product that brought in the most revenue, with how much it earned. The categories can come back in any order.

Every category crowns exactly one earner. Find who wears it.

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 problem really is

Strip the merchandising costume and this is a top-1-per-group: an arg max within each category. Anyone can total revenue per product. What separates candidates is returning the product that owns the max, not just the max number. The tempting move, GROUP BY category with MAX(revenue), hands you the winning amount but loses the product_name attached to it. Reach for MAX and you can report a dollar figure you cannot name.

Break down the requirements

Step 1: Get revenue to the product grain

Revenue lives in transactions, category lives in products, so join on product_id and SUM(total_amount) grouped by category and product_name. This collapses many transactions into one revenue figure per product, which is the unit the ranking will compare.

Step 2: Rank inside each category, not globally

PARTITION BY category restarts the ordering per category so a strong Electronics product never crowds out the top Books product. ORDER BY product_revenue DESC puts the earner first. RANK (not ROW_NUMBER) is deliberate: if two products tie for the top of a category, both should be crowned, and RANK gives them both position 1.

Step 3: Keep only the winners

Filter to category_rank = 1 to keep one winner per category, or several on a tie, and return the three columns the question asks for: the category, the winning product_name and its product_revenue. The analyst takes the categories in any order, so the query adds no sort, and the grader accepts the rows in whatever order they arrive.

The solution

WITH product_revenue AS (
  SELECT p.category AS category,
         p.product_name AS product_name,
         SUM(t.total_amount) AS product_revenue
  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,
         product_revenue,
         RANK() OVER (PARTITION BY category ORDER BY product_revenue DESC) AS category_rank
  FROM product_revenue
)
SELECT category,
       product_name,
       product_revenue
FROM ranked
WHERE category_rank = 1

MAX in a plain GROUP BY

SELECT category, MAX(revenue) FROM ... GROUP BY category returns the winning amount but strips product_name. Re-joining that max back to find the name reintroduces the tie problem and an extra pass.

Window rank

One aggregation, one windowed pass, then a filter. The product_name rides along with its revenue the whole way, and ties fall out naturally from RANK.

Common follow-up questions

• Now give me the top three products per category instead of just the leader. (Tests whether they change the filter to `category_rank` <= 3 and understand `DENSE_RANK` versus RANK for gapped positions.)
• What if 'best-selling' means units sold rather than revenue? (Probes whether they can swap SUM(`total_amount`) for SUM(quantity) without disturbing the ranking structure.)
• How would you restrict this to the most recent year without risking empty categories? (Pushes toward a `transaction_date` filter pushed into the base scan and reasoning about categories that had no sales in the window.)