Product Revenue Share of Marketplace
What percentage of total marketplace revenue does each product account for?
- Joins
- Subqueries
- Aggregation
- Numeric functions
- NULL handling
Challenge brief
Understand the request
Finance Team is building a product P&L dashboard and needs to know each product's share of total marketplace revenue.
Show each product's revenue and its share of total marketplace revenue as a percentage.
Return
- product_name
- category
- product_revenue (rounded to 2 decimals)
- revenue_pct — percentage of total revenue (rounded to 2 decimals)
Constraints
- Revenue is quantity multiplied by the recorded order-line price
- Revenue share is product revenue divided by total marketplace revenue, expressed as a percentage
- The calculation must remain safe when total marketplace revenue is zero
- Only products that appear in at least one order
- Order by product_revenue descending
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
products
product_idINTEGERproduct_nameVARCHAR(200)categoryVARCHAR(100)
order_items
product_idINTEGERquantityINTEGERpriceREAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Product names are in products. Revenue per line item is price * quantity in order_items. You need two things: per-product revenue (GROUP BY), and total revenue across all products (a scalar value). Use a scalar subquery in the SELECT list for the total.
Hint 2
Group order-line revenue by product. Use a scalar subquery for total revenue and wrap that denominator in NULLIF(total, 0).
Hint 3
Scaffold: group joined order-line revenue by product, divide each group by a NULLIF-protected scalar total, and order product revenue descending.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT p.product_name, p.category, ROUND(SUM(oi.price * oi.quantity), 2) AS product_revenue, ROUND(SUM(oi.price * oi.quantity) * 100.0 / NULLIF((SELECT SUM(price * quantity) FROM order_items), 0), 2) AS revenue_pct FROM products p INNER JOIN order_items oi ON p.product_id = oi.product_id GROUP BY p.product_id, p.product_name, p.category ORDER BY product_revenue DESC;Why this works
The scalar subquery returns total marketplace revenue. Each product's grouped revenue is divided by that total, with NULLIF protecting an empty or zero-revenue fixture from division by zero. Multiplying by 100.0 preserves decimal division.
Success check
8 products — Bluetooth Speaker leads at 25.32% of total revenue
Expected result
Use this output to verify values, aliases, ordering, and row count.
| product_name | category | product_revenue | revenue_pct |
|---|---|---|---|
| Bluetooth Speaker | Electronics | 179.97 | 25.32 |
| Coffee Maker | Home & Kitchen | 159.98 | 22.51 |
| Laptop Stand | Electronics | 91.98 | 12.94 |
| Wireless Mouse | Electronics | 89.97 | 12.66 |
| Yoga Mat | Sports | 74.97 | 10.55 |
| Python Programming Book | Books | 39.99 | 5.63 |
| USB-C Cable | Electronics | 38.97 | 5.48 |
| Desk Lamp | Home & Kitchen | 34.99 | 4.92 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Airbnb
Independent Airbnb-style marketplace, booking, listing, payment, review, and guest analytics SQL practice.
Uber
Independent Uber-style mobility marketplace SQL practice covering trips, drivers, riders, pricing, payments, and promotions.
Microsoft
Independent Microsoft-style cloud, productivity, subscription, usage, support, and customer analytics SQL practice.
Return to the complete interview preparation experience.