First Among Equals

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

Problem

The merchandising team wants to see the products that move the most units, counting every unit across all of a product's orders. Return each product whose unit total matches the highest any product reached, showing how many orders it took and the units it moved.

When more than one product wears the crown, name every one of 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

What this really is

Strip the leaderboard costume off and this is an argmax that refuses to drop its ties. You total each product's units, then return every product sitting at the top total, not one arbitrary winner. Two things sink most attempts. First, ranking by the number of orders instead of units: on this data every product has the same order count, so a count can never separate them and the whole catalog ties. Second, reaching for 'sort descending, take the top row', which silently throws away the co-leaders. The correct shape is two passes: learn the winning total, then return everyone who hit it.

The naive answer and why it fails

Naive: LIMIT 1

GROUP BY product ORDER BY SUM(quantity) DESC LIMIT 1. It returns one product and looks right. The instant a second product ties on units, that product vanishes from the result with no error and no warning. You would ship a best-seller report that hides half the winners.

Correct: equal to the max

GROUP BY product, then HAVING SUM(quantity) = (the max unit total across all products). Every product at the peak survives, whether that is one product or fifty. The result is stable no matter how many share the lead.

SELECT p.product_name, COUNT(*) AS order_count, SUM(t.quantity) AS units_sold
FROM transactions t
JOIN products p ON t.product_id = p.product_id
GROUP BY p.product_id, p.product_name
HAVING SUM(t.quantity) = (
  SELECT MAX(units) FROM (
    SELECT SUM(t2.quantity) AS units
    FROM transactions t2
    JOIN products p2 ON t2.product_id = p2.product_id
    GROUP BY t2.product_id
  )
)

Step 1: Total each product's units

Join to products on product_id for the name, group per product, and add up quantity for the unit total. COUNT(*) rides along as context: how many orders produced those units. Notice this is SUM(quantity), not COUNT(*): counting orders answers a different question, and here every product has the same order count, so a count could never rank them.

Step 2: Find the winning total as a scalar

The inner query repeats the same per-product unit total, then wraps it in MAX(units) to collapse all those totals into one number: the peak. It joins to products for the same reason the outer query does. Skip that join and the max is computed over product_ids that may not exist in the catalog, so the peak can belong to a product that never appears in the joined output, and nothing matches. It has to be its own aggregation because you cannot take MAX of an already grouped SUM in one level without a nested query.

Step 3: Keep only the products at the peak

HAVING SUM(quantity) = (that scalar) filters the grouped rows down to exactly the products sitting at the top. Ties survive naturally because equality does not care how many rows satisfy it. ORDER BY product_name then gives the stable alphabetical output the preview shows.

Common follow-up questions

• How would you also return the second-best tier of products, not just the top? (Pushes the candidate from a scalar max toward `DENSE_RANK` over the per-product totals.)
• What changes if best-selling should mean the most revenue instead of the most units? (Tests whether they can swap SUM(quantity) for SUM(`total_amount`) and understand the metric distinction.)
• How would you break ties and return exactly one product deterministically? (Explores tie-break keys such as highest `total_amount` or earliest `transaction_date`.)