# First Among Equals > When more than one product wears the crown, name every one of them. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem The merchandising team is drawing up a leaderboard of the products that move the most units, counting every unit across all of a product's orders. Return every product tied for the highest unit total, showing how many orders it took and the units it moved, listed alphabetically by name. ## 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 move that cracks it** > > Compute the peak unit total as a single scalar first, then keep only the groups whose total equals it. That equality test in HAVING is what preserves ties. A sort plus a one-row limit cannot say 'and anyone else who matched', because it has already discarded the runners-up. ### 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. **All products tied at the top** ```sql 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 ) ) ORDER BY p.product_name, p.product_id ``` *Total units per product, then a scalar max in HAVING keeps every co-leader.* #### 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. > **Ranking by orders, or taking one row** > > Ranking by order count is the classic miss here: every product has the same number of orders, so a count-based leaderboard reports the entire catalog as tied. Units per product is the quantity that actually varies. The other miss is ORDER BY ... LIMIT 1, which crowns one product and quietly hides the others that share the top total. > **What the interviewer is watching for** > > The tell of seniority is asking 'do you want just one top seller or everyone tied for it?' before writing anything, then choosing HAVING = MAX over LIMIT 1 on purpose. Reaching straight for ORDER BY ... LIMIT 1 without acknowledging ties reads as someone who has not been burned by a hidden co-leader in production. > **Two scans, both cheap** > > The engine builds the per-product unit totals once for the scalar max and once for the outer group. With an index on transactions.product_id both are index-friendly aggregations, and the inner result is a single value the planner materializes once, not a correlated per-row lookup. At catalog scale (thousands of products, millions of transactions) this stays a couple of grouped scans, well within interactive latency. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/first_among_equals) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.