# The Standings > Every category earns its place on the board. Find where each one lands. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L5 ## Problem Strategy wants a report card for every product category that has actually sold something. For each one, total up the revenue and the units moved, then give it a standing by revenue with the biggest earners on top and revenue ties sharing a standing. ## Worked solution and explanation ### What this problem is really about Underneath the report-card framing this is one question: when two categories earn the same revenue, what number does the next one get? That is the whole problem. Anyone can join, sum, and sort. What separates candidates is reaching for `RANK()` instead of `ROW_NUMBER()` or `DENSE_RANK()`. `ROW_NUMBER()` hands tied categories different positions and quietly breaks the tie you were told to keep; `DENSE_RANK()` keeps the tie but refuses to leave a gap, so a standing stops reflecting how many categories actually outsold a given one. Pick wrong and the standings look plausible and are silently incorrect. > **Trick to Solving** > > "Ties sharing a position" is the signal for `RANK()` or `DENSE_RANK()`. Since a standing implies gaps after a tie (two categories tied for 2nd means the next is 4th), `RANK()` is the natural choice. Spot this by watching for language about ties or shared positions. > > 1. Join and aggregate revenue and units per category > 2. Apply `RANK() OVER (ORDER BY revenue DESC)` to assign positions > 3. No subquery needed since the window function runs after GROUP BY --- ### Break down the requirements #### Step 1: Join products to transactions Join on `product_id` to associate `category` with transaction amounts and quantities. #### Step 2: Aggregate per category `SUM(t.total_amount)` for revenue and `SUM(t.quantity)` for total units sold, grouped by `p.category`. #### Step 3: Rank by revenue `RANK() OVER (ORDER BY SUM(t.total_amount) DESC)` assigns positions with ties sharing the same rank and the next lower total skipping ahead. --- ### The solution **Aggregate with inline window ranking** ```sql SELECT p.category, SUM(t.total_amount) AS total_revenue, SUM(t.quantity) AS total_units, RANK() OVER (ORDER BY SUM(t.total_amount) DESC) AS position FROM products p JOIN transactions t ON p.product_id = t.product_id GROUP BY p.category ``` > **Cost Analysis** > > Hash join of 50K products to 250M transactions, then GROUP BY reduces to ~30 categories. The RANK window function sorts ~30 rows, which is negligible. The bottleneck is scanning 250M transaction rows. > **Interviewers Watch For** > > Whether you place the window function directly in the SELECT of the GROUP BY query (correct) or wrap it in an unnecessary subquery. SQL evaluates window functions after GROUP BY, so this works in a single level. > **Common Pitfall** > > Using `ROW_NUMBER()` instead of `RANK()` assigns unique positions even to tied-revenue categories, violating the requirement that ties share a position. `DENSE_RANK()` keeps the tie but never skips, so the standing understates how many categories outsold the ones below it. In the sample, Garden and Music both earn 117.75 and share position 2, so the next category lands at position 4, which only `RANK()` produces. --- ## Common follow-up questions - What is the difference between RANK and DENSE_RANK here? _(RANK skips numbers after ties (1,1,3); DENSE_RANK does not (1,1,2). Tests precise understanding.)_ - What if categories with no transactions should appear with zero revenue? _(Tests LEFT JOIN from products and COALESCE for NULL sums.)_ - How would you show only the top 3 positions including all ties? _(Wrap in a subquery and filter WHERE position <= 3.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_standings) - [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.