Kings of the Aisle

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

Visa

Problem

The merchandising team is reviewing which product owned each category this year. For every category, surface the single product with the highest total sales, listed from the biggest earner down.

Every category crowns exactly one winner. Go find them.

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

Why this problem exists in real interviews

This is a top-1-per-group problem wearing a merchandising costume, and it is the exact spot Visa-style SQL rounds probe your window-function instinct. Anyone can join transactions to products and sum revenue by category and product. The real question is how you collapse each category down to its single leader. Reach for MAX(total_revenue) in a second aggregation and you get the right number but lose the product_name attached to it, or you re-join and accidentally surface two products when a category has a tie. The clean move is to number the rows inside each category and keep number one.

The schema you are reasoning about

Break down the requirements

Step 1: Join to reach the category

category and product_name live on products, the revenue lives on transactions. Inner join on product_id so every sale carries its category label. An inner join is correct here: a transaction with no matching product cannot be attributed and should drop.

Step 2: Roll up to product-in-category revenue

Filter to this year with strftime('%Y', transaction_date) = '2026', then GROUP BY category, product_name and SUM(total_amount). This is the grain the leaderboard actually cares about, one row per product per category.

Step 3: Number within each category and keep the leader

ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_revenue DESC, product_name) stamps the top earner in each category as 1. Filtering rn = 1 gives exactly one product per category, and the product_name rides along untouched, which a bare MAX() would have severed.

The solution

WITH product_revenue AS (
  SELECT p.category AS category,
         p.product_name AS product_name,
         SUM(t.total_amount) AS total_revenue
  FROM transactions t
  JOIN products p ON t.product_id = p.product_id
  WHERE strftime('%Y', t.transaction_date) = '2026'
  GROUP BY p.category, p.product_name
),
ranked AS (
  SELECT category,
         product_name,
         total_revenue,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_revenue DESC, product_name) AS rn
  FROM product_revenue
)
SELECT category, product_name, total_revenue
FROM ranked
WHERE rn = 1
ORDER BY total_revenue DESC

Common follow-up questions

• Now return the top three products per category instead of just the leader. (Tests whether they see rn = 1 generalizes to rn <= 3 with no structural change.)
• How would you handle categories that had zero sales this year but should still appear in the report? (Pushes toward starting from products (or a category dimension) with a LEFT JOIN so empty categories survive.)
• The team wants each product's share of its category's total revenue alongside the leader. (Tests SUM() OVER (PARTITION BY category) as a second window in the same pass.)