The Crown Jewels

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

L5

Asked in

FinThrive

Problem

Across our whole catalog and all of its sales history in transactions, we want the products that have brought in the most money. List the top 5 by total revenue with their names from products, biggest earner first.

Revenue crowns the winners. Who sold the most?

Tables

products

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

transactions

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

Expected output

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, and AVG(total_amount) ranks them by ticket size. On this data nearly every product sold exactly twice, so a count-based list is a pile of ties, and an average reports half the money each leader actually brought in. 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 ship the wrong winners or the wrong numbers 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, and sales with a missing product_id cannot be credited to anyone, so dropping both costs nothing here.

Step 2: Sum the money, do not count the rows

total_amount is the dollar value of each sale, refunds and voided sales included, 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, sort 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

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
LIMIT 5

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 'top 5' implies a strict cap or tie inclusion, and how you would express ties if asked.)
• If a product has no transactions, should it appear with zero revenue? (Tests LEFT JOIN vs INNER JOIN; a top-5 revenue list makes zero-revenue products irrelevant.)