The Heavy Hitters

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L4

Asked in

Amazon

Problem

An analyst suspects the catalog only looks diverse and that a handful of products carry most of the sales recorded in transactions, where a product's revenue is its total amount across every sale. Build her a leaderboard of the top 10 positions, each product shown with its name from products, its total, and its position on the board.

Total sales tell the real story. Surface the products that carry the catalog.

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

This is a group-sum with a rank bolted on, wearing a leaderboard costume. Everyone totals the sales per product. The trap is reading 'top 10' as ORDER BY total DESC LIMIT 10 and stopping there: LIMIT slices rows but never writes a number, so the position column the board needs simply does not exist in your output. And if you fake one with a row counter, two products with the same total get different positions and the one that lands at 11 silently falls off the board.

Building it up

Step 1: Total each product

Join transactions to products on product_id, then GROUP BY p.product_name with SUM(t.total_amount). That collapses many sales into one total per product, and names this intermediate product_revenue so the next step reads plainly.

Step 2: Assign positions

Over product_revenue, DENSE_RANK() OVER (ORDER BY total_total_amount DESC) stamps a position on each total, biggest first. Ranking the named total, not re-writing the SUM, keeps every expression defined once.

Step 3: Keep the top 10 positions

A window function cannot be filtered in the SELECT that defines it, so the ranking lives in its own CTE, ranked, and the outer query keeps rnk <= 10. That filter is on standing, so a tie at position 10 keeps every tied product.

The solution

WITH product_revenue AS (
    SELECT
        p.product_name,
        SUM(t.total_amount) AS total_total_amount
    FROM transactions t
    JOIN products p ON t.product_id = p.product_id
    GROUP BY p.product_name
),
ranked AS (
    SELECT
        product_name,
        total_total_amount,
        DENSE_RANK() OVER (ORDER BY total_total_amount DESC) AS rnk
    FROM product_revenue
)
SELECT product_name, total_total_amount, rnk
FROM ranked
WHERE rnk <= 10

`ROW_NUMBER()`

Say two products both total 2,606.22. They get positions 4 and 5 in whatever order the engine picks, and the next product jumps to 6. Ties are broken by luck.

`DENSE_RANK()`

Both products at 2,606.22 get position 4 and the next product is 5. Equal totals mean equal standing, and rnk <= 10 keeps or drops them together.

Common follow-up questions

• If three products tie for position 10, your board returns 12 rows. Is that what you want, and what changes if the business insists on exactly 10 names? (Tests whether the candidate understands that `DENSE_RANK` ties can push the row count past the stated limit.)
• Why is the `DENSE_RANK` sort cheap here even though `transactions` has 150M rows? (Tests understanding of ranking a collapsed aggregate versus raw rows.)
• Now they want this board for a single month. Where does that filter go, and does the `transaction_date` partitioning help? (Tests whether the candidate can scope the leaderboard to a window and reason about the partition key.)
• How would you produce the top 3 within each `category` instead of the top 10 overall? (Tests whether the candidate can extend the same shape to per-group standings.)