# The Heavy Hitters > Total sales tell the real story. Surface the products that carry the catalog. Canonical URL: 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. ## Tables ### transactions - transaction_id INTEGER - user_id INTEGER - product_id INTEGER - quantity INTEGER - total_amount REAL - transaction_date TEXT Sample rows (10 of 200): | transaction_id | user_id | product_id | quantity | total_amount | transaction_date | | --- | --- | --- | --- | --- | --- | | 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 | | 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 | | 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 | | 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 | | 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 | | 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 | | 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 | | 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 | | 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 | | 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 | ### products - product_id INTEGER - product_name TEXT - category TEXT - price REAL - rating REAL - in_stock INTEGER Sample rows (10 of 200): | product_id | product_name | category | price | rating | in_stock | | --- | --- | --- | --- | --- | --- | | 1001 | Basic Device 1X | Books | 17.52 | 2.7 | 1 | | 1050 | Deluxe Bundle 2X | Clothing | 25.05 | 4.4 | 1 | | 1099 | Pro Unit 3X | Home & Kitchen | 32.58 | 2.1 | 1 | | 1148 | Ultra Tool 4X | Collectibles | 40.11 | 3.8 | 1 | | 1197 | Essential Set 5X | Toys | 47.64 | 1.5 | 1 | | 1246 | Classic System 6X | Beauty | 0 | 3.2 | 1 | | 1295 | Smart Gadget 7X | Automotive | 62.7 | 4.9 | 0 | | 1344 | Eco Kit 8X | Garden | 70.23 | 2.6 | 1 | | 1393 | Turbo Pack 9X | Music | 77.76 | 4.3 | 0 | | 1442 | Premium Widget 10X | Electronics | 85.29 | 2 | 1 | ## Expected output First 5 of 10 rows: | product_name | total_total_amount | rnk | | --- | --- | --- | | Premium Widget 100X | 2713.98 | 1 | | Eco Kit 98X | 2660.1 | 2 | | Smart Gadget 97X | 2633.16 | 3 | | Classic System 96X | 2606.22 | 4 | | Essential Set 95X | 2579.28 | 5 | ## 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. > **The position is a column, not a cut** > > The moment the ask says 'position on the board', you are choosing between three tools that look interchangeable. `LIMIT 10` cuts by row count and emits no position. `ROW_NUMBER()` invents a unique order even for equal totals. `DENSE_RANK()` gives equal totals the same position and never leaves a gap, which is what 'standing is the total' means. --- ### 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 **Revenue leaderboard** ```sql 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 ``` > **`LIMIT` lets row order pick the winner** > > With `LIMIT 10`, a tie at the boundary is settled by whatever row order the engine happens to produce: one of two products with the same total makes the board and the other vanishes, with nothing in the output telling you. Filtering on a `DENSE_RANK` value makes 'top 10' mean standing, not 'first 10 rows returned'. **`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. > **Rank after you shrink** > > The window runs over `product_revenue`, tens of thousands of product totals, not 150M sale rows. The expensive part is the aggregation; once totals collapse to one row per product, the sort behind `DENSE_RANK` is small. > **Name the tie rule before the query** > > The tell is whether you can say, before typing, why `DENSE_RANK` and not `ROW_NUMBER` or `LIMIT`. Candidates who pick the ranking function on purpose read as senior; candidates who default to `LIMIT` and get lucky on tie-free data do not. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_heavy_hitters) - [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.