The Ones That Move

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

DoorDash

Problem

The merchandising team wants to know which products carry each category by sales, where a product's revenue is its quantity times unit price summed across every order line. For each category return its three strongest products, showing the category, the product name, that revenue, and the product's standing within the category, ordered by category, then standing, then product name.

Every aisle has its champions. Surface the three that carry each one.

Tables

order_items

Columns: item_id INTEGER, order_id INTEGER, product_id INTEGER, user_id INTEGER, quantity INTEGER, unit_price REAL

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-N-per-group problem wearing a merchandising costume. The skill being probed: can you rank products inside each category and keep only the leaders, without flattening the categories together or trying to filter a ranking where it cannot be filtered? Anyone can sum revenue and sort it. What separates candidates is knowing that the per-category cutoff has to happen against a computed rank in an outer query, and choosing the ranking function whose tie behavior matches what 'top three' actually means.

Break down the requirements

Step 1: Get revenue to the right grain

Revenue is not a stored column. It is SUM(quantity * unit_price) per product, computed by joining order_items to products and grouping by category and product_name. Multiply first, then sum: SUM(quantity) * SUM(unit_price) is a different, wrong number.

Step 2: Rank within each category, not globally

PARTITION BY category restarts the ranking inside every category, so each aisle gets its own 1, 2, 3. Drop the PARTITION BY and you get a single global leaderboard, which silently answers a different question.

Step 3: Filter the rank in an outer query

You cannot put revenue_rank <= 3 in the same SELECT that defines it; window functions are evaluated after WHERE. Wrap the ranking in a CTE, then filter revenue_rank in the outer query. Keep that rank column in the final SELECT so the output shows each product's standing, and order by category, then standing, then product name so ties resolve the same way every run.

The solution

WITH product_revenue AS (
  SELECT p.category,
         p.product_name,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.category, p.product_name
),
ranked AS (
  SELECT category,
         product_name,
         revenue,
         DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS revenue_rank
  FROM product_revenue
)
SELECT category, product_name, revenue, revenue_rank
FROM ranked
WHERE revenue_rank <= 3
ORDER BY category, revenue_rank, product_name;

Common follow-up questions

• What changes if the team wants exactly three rows per category even when there are ties? (Pushes toward `ROW_NUMBER` plus a deterministic tiebreaker, and a discussion of why that answer is less defensible.)
• How would you restrict this to revenue from the last 90 days? (Tests adding a date predicate and where it lives; `order_items` has no date, so they must join through orders or a transaction date and reason about partition pruning.)
• Now return each category's share of total revenue alongside the top three. (Stretches into a second window (SUM OVER PARTITION BY category) or a separate aggregate joined back, and ratio computation.)