Product Revenue Ranking

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

L3

Asked in

FinThrive

Problem

Our homepage feature is supposed to show the five products that bring in the most revenue, but the current pick was made by hand and we don't trust it. From the sales recorded in transactions, show each of those products with its name, category and total revenue, highest first.

Rank them by revenue. See who leads.

Tables

products

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

transactions

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

Expected output

Worked solution and explanation

This is a top-five over a one-to-many rollup dressed up as a homepage pick. Anyone can write the join. What separates candidates is where the revenue number comes from. transactions already books what each sale earned in total_amount, refunds and voids included. Rebuild it as quantity * price from the catalog and you feature a different five products.

Where the revenue lives

Step 1: Total the booked amount per product

Every top product sells twice on this seed, so the answer is a per-product sum, not a sort over sales rows. Sort raw rows and 'Premium Widget 100X' shows up twice at 1356.99 instead of once at 2713.98. Sum total_amount, and keep p.product_id in the GROUP BY so two products that ever share a name stay separate.

Step 2: Bring the display columns from products

product_name and category exist only in products; the amounts exist only in transactions. An inner join on product_id is the right shape: a product with no sales has no revenue to feature, and the 18 sales with a NULL product_id match nothing and drop out.

Step 3: Sort by the total and cut at five

ORDER BY total_revenue DESC LIMIT 5. The sixth product sits at 2552.34, well clear of the fifth at 2579.28, so no tie straddles the cut on this data and no tiebreaker is needed.

SELECT p.product_name, p.category, SUM(t.total_amount) AS total_revenue
FROM products p
JOIN transactions t ON p.product_id = t.product_id
GROUP BY p.product_id, p.product_name, p.category
ORDER BY total_revenue DESC
LIMIT 5

Recomputed from the catalog

SUM(t.quantity * p.price) trusts the list price. Five products that sold have a NULL price, so their sales vanish from the sum. 'Classic System 6X' lists at 0.0 while its sale booked 90.81. The resulting top five is a different list.

Booked on the sale

SUM(t.total_amount) adds what each sale recorded. The -45.00 refund and the 0.00 voided sale land against their products, and the five totals match what the ledger says those products earned.

Common follow-up questions

• How would you feature the top five per `category` instead of overall? (Moves the cut inside each group.)
• Two products tie for fifth: how do you decide which one is featured, and where does the query say it? (Tests a deterministic tie rule placed before `LIMIT`.)
• Should the -45.00 refund count against its product, or should the feature use gross sales only? (Tests gross versus net revenue definitions on `total_amount`.)