The Standings

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

L5

Problem

In these standings, categories that moved the same number of units share a standing, and the next category's standing still counts every category above it. A merchandising analyst needs every category's total revenue and total units from transactions, each sale's category coming from products, along with its standing by units with the biggest mover in first place.

Every category earns its place on the board. Find where each one lands.

Tables

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

What this problem is really about

Under the merchandising framing this comes down to one question: when two categories move the same number of units, what standing does the next one get? Anyone can join, sum and sort. The skill is numbering standings so that a tie shares a place and the place after it still counts every category above. On this data that matters twice: 'Home & Kitchen' and 'Garden' both move 72 units, and 'Electronics' and 'Collectibles' both move 20. ROW_NUMBER() splits each pair. DENSE_RANK() keeps each pair together but closes the gap, so 'Sports' reads 3 instead of 4 and 'Toys' reads 9 instead of 11. In both cases the standings look plausible and are wrong from the third row down.

Build it in three moves

Step 1: Join transactions to products

Join on product_id to give each sale its category. Make it an inner join: 18 sales carry no product_id, and an outer join from transactions would turn them into a twelfth row with a NULL category.

Step 2: Total each category

SUM(t.total_amount) is the revenue and SUM(t.quantity) is the units, grouped by p.category. The refund and the voided sale stay in, because they are recorded sales and the totals are what was recorded.

Step 3: Place categories by units

RANK() OVER (ORDER BY SUM(t.quantity) DESC) reads the grouped total directly, because window functions run after GROUP BY. The descending direction puts the biggest mover in place 1.

The solution

SELECT
    p.category,
    SUM(t.total_amount) AS total_revenue,
    SUM(t.quantity) AS total_units,
    RANK() OVER (ORDER BY SUM(t.quantity) DESC) AS position
FROM products p
JOIN transactions t ON p.product_id = t.product_id
GROUP BY p.category

Gapless numbering

DENSE_RANK() by units: 'Music' 1, 'Home & Kitchen' 2, 'Garden' 2, 'Sports' 3, then down to 'Electronics' 8, 'Collectibles' 8, 'Toys' 9.

Standings

RANK() by units: 'Music' 1, 'Home & Kitchen' 2, 'Garden' 2, 'Sports' 4, then down to 'Electronics' 9, 'Collectibles' 9, 'Toys' 11.

Common follow-up questions

• What is the difference between `RANK()` and `DENSE_RANK()` on this data? (`RANK()` gives 1, 2, 2, 4 and `DENSE_RANK()` gives 1, 2, 2, 3. This tests whether the candidate can say which one standings need.)
• What if categories with no sales had to appear with zero units? (This tests a `LEFT JOIN` from `products` with `COALESCE` on the sums, and where zero lands in the standings.)
• How would you show only the top three standings, ties included? (Wrap the query and filter on `position <= 3`. With the tie at 2, that returns three categories.)