Find the Dominant Seller
Reported by candidates from SquadStack.ai's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
SquadStack.ai reported this SQL question in September 2026, and it looks like a ranking problem but it isn't. It reduces to one comparison: is a seller's revenue bigger than everyone else's combined? That's a grouping query with a grand total and a LEFT JOIN so zero-order sellers still count. If the OA lands in your inbox this week, this is a short query once you see it. StealthCoder sits invisibly on your screen as a safety net if you blank mid-assessment, but the idea below is small enough to hold in your head.
The problem
The table sellers stores one row per seller, and orders stores completed orders. For each seller, compute total_revenue = SUM(item_qty * item_rate) across that seller's orders. A seller with no orders has total revenue 0. A seller is dominant when their total revenue is strictly greater than the combined total revenue of all other sellers. Return every dominant seller's seller_id and total_revenue, ordered by seller_id ascending. sellersColumnMeaning seller_idUnique seller identifier seller_nameSeller name ordersColumnMeaning order_idUnique order identifier seller_idSeller that received the order item_qtyNumber of items sold item_rateExact rate per item Tables sellers: seller_id PK (Integer), seller_name (Text) orders: order_id PK (Integer), seller_id (Integer), item_qty (Integer), item_rate (Decimal) Constraints The sellers table has between 1 and 10^5 rows. The orders table has between 0 and 2 * 10^5 rows. seller_id and order_id are unique and non-null. Every orders.seller_id references an existing seller. seller_name, item_qty, and item_rate are non-null. 0 <= item_qty <= 10^6. 0 <= item_rate <= 10^9, with at most 12 digits after the decimal point.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick: dominant means revenue > (grand_total - revenue). No self-join, no pairwise comparison. Step one, LEFT JOIN sellers to orders and compute COALESCE(SUM(item_qty * item_rate), 0) per seller in a CTE. Step two, get the grand total with SUM(total_revenue) OVER () or a scalar subquery. Step three, filter where total_revenue > grand_total - total_revenue, then ORDER BY seller_id. The common pitfall is an INNER JOIN, which drops sellers with no orders. Since a seller with zero revenue can never be dominant, that rarely changes the answer, but if every seller has zero revenue, nothing should return, and 0 > 0 is false, so the filter handles it. The other trap is decimal precision. item_rate has up to 12 decimal places, so don't cast to float. Keep it DECIMAL. If you freeze on the window function syntax during the live OA, StealthCoder can supply it, but you should be able to write this unaided.
StealthCoder is the hedge for the one pattern you didn't drill. It runs invisibly during the screen share.
You can drill Find the Dominant Seller cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. If you're reading this with an OA window open, you're who this was built for.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass SquadStack.ai's OA.
SquadStack.ai reuses patterns across OAs. If you're reading this with an OA window open, you're who this was built for. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Find the Dominant Seller FAQ
What's the trick in the SquadStack.ai Find the Dominant Seller question?+
Rewrite the condition. Revenue strictly greater than all others combined means revenue > grand_total - revenue. Compute per-seller totals once, compute the grand total once, then compare. You never need to join sellers against each other or loop through them.
Do I need a LEFT JOIN or is INNER JOIN fine?+
Use LEFT JOIN with COALESCE to 0. The spec says sellers with no orders have revenue 0, and the safe reading is to keep them in the totals. A zero-revenue seller can't be dominant anyway, but LEFT JOIN matches the spec and avoids edge-case surprises.
Should I use a window function or a subquery for the grand total?+
Either works. SUM(total_revenue) OVER () in a CTE is clean and readable. A scalar subquery over the CTE also works. At 10^5 sellers and 2 * 10^5 orders, performance is not a concern for either approach.
What edge cases break this query?+
Two cases. First, all sellers at zero revenue: 0 > 0 is false, so you correctly return nothing. Second, precision. item_rate can have 12 decimal places, so keep DECIMAL types and avoid float casts. Also remember the strict greater-than, not greater-or-equal.
How do I prepare for this in 48 hours?+
Write it from scratch twice: LEFT JOIN plus GROUP BY, then the window total, then the filter. Practice COALESCE and OVER () until they're automatic. This pattern, per-group total versus the remainder, shows up in many SQL assessments, so it's worth the hour.