# The Crown Jewels > Revenue crowns the winners. Who sold the most? Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L5 · Asked in: FinThrive ## Problem Pull the top 5 revenue-generating products across the entire catalog. Show the product name and total revenue. ## Tables ### 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 | ### transactions - transaction_id INTEGER - product_id INTEGER - total_amount REAL - user_id INTEGER - quantity INTEGER - 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 | ## Expected output First 5 of 5 rows: | product_name | total_revenue | | --- | --- | | Premium Widget 100X | 2713.98 | | Eco Kit 98X | 2660.1 | | Smart Gadget 97X | 2633.16 | | Classic System 96X | 2606.22 | | Essential Set 95X | 2579.28 | ## Worked solution and explanation ### What this really is This is a sum-per-group wearing a leaderboard costume, and the word revenue is doing more work than it looks. Revenue is SUM(`total_amount`): COUNT ranks products by how often they sell, AVG ranks them by ticket size, and because transaction frequency and transaction amount are skewed independently on this data, each of those crowns a different five products than the true revenue leaders. There is a quieter trap underneath: group by `product_name` and two distinct products that happen to share a name fuse into one inflated row. Miss either and you crown the wrong winners with a query that looks perfectly clean. --- ### Working through it #### Step 1: Join and carry the name Each transaction stores only `product_id`, so join transactions to products on `product_id` to carry `product_name` alongside every sale. An inner join is the right call: a product with no sales cannot be a revenue leader, so dropping unsold products costs nothing here. #### Step 2: Sum the money, do not count the rows `total_amount` is the dollar value of each sale, so SUM(`t.total_amount`) per product is revenue. COUNT(*) answers 'sold most often' and AVG answers 'biggest ticket', and neither is the question being asked. #### Step 3: Group on identity, then take the top five Group by `product_id` (carrying `product_name` for display) so same-named products stay separate, order by revenue highest first, and cap the result at 5. Grouping on the name alone would silently merge distinct products into one row. --- ### The solution **Join products to transactions, sum revenue per product, top 5 by total** ```sql SELECT p.product_name, SUM(t.total_amount) AS total_revenue FROM products p JOIN transactions t ON t.product_id = p.product_id GROUP BY p.product_id, p.product_name ORDER BY total_revenue DESC, p.product_name ASC LIMIT 5 ``` > **Interviewers Watch For** > > The tell is whether you aggregate before you sort. Sorting the raw joined rows gives per-sale amounts, not per-product totals. The correct grain is one row per `product_id`, and the ORDER BY runs on the computed SUM, not on any single transaction. > **Common Pitfall** > > The wrong aggregate. SUM is revenue, COUNT is sales volume, AVG is ticket size. On this skewed data those three functions rank three different sets of products, so picking the right one is the whole problem, not a detail. > **Cost Analysis** > > The real cost is scanning transactions (50M rows) and aggregating down to roughly 13K product groups; the 15K-row products table is trivial by comparison. A covering index on transactions(`product_id`, `total_amount`) lets the engine aggregate without touching the base rows, and only the top 5 survive the LIMIT. --- ## Common follow-up questions - If two products share the same `product_name` but different `product_ids`, does your GROUP BY merge them? _(Tests grouping granularity; GROUP BY `product_name` merges them, which may or may not be desired.)_ - Should LIMIT 5 include ties at position 5, or is truncation acceptable here? _(Tests whether the prompt's 'top 5' implies strict limit or tie inclusion.)_ - If a product has no transactions, should it appear with zero revenue? _(Tests LEFT JOIN vs INNER JOIN; the prompt says 'top 5 revenue-generating', implying zero-revenue products are irrelevant.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_crown_jewels) - [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.