SQL interview question: products that make 80% of revenue
Problem
Shared data rulesFor each category, find the fewest top-earning products that together bring in at least 80% of its September 2026 revenue. Also find their share of the products sold and the share of revenue they bring.
Formulas
top_products_share = top_products / productstop_revenue_share = these products’ revenue / category revenue
Example
orders| order_id | status | created_at |
|---|---|---|
| 501 | delivered | 2026-09-05 13:00 |
| 502 | delivered | 2026-09-01 10:30 |
order_items| order_id | product_id | category | quantity | price_usd | Row note |
|---|---|---|---|---|---|
| 501 | 11 | Beverages | 2 | 4 | |
| 501 | 13 | Beverages | 1 | 3 | ← tie with 14: smaller product_id |
| 502 | 12 | Beverages | 2 | 3 | |
| 502 | 14 | Beverages | 1 | 3 |
- Row 2: tie with 14: smaller product_id
| category | products | top_products | top_products_share | top_revenue_share |
|---|---|---|---|---|
| Beverages | 4 | 3 | 0.75 | 0.85 |
Revenue: 11 — $8, 12 — $6, 13 and 14 — $3 each, out of $20. Running shares 0.4, 0.7, 0.85: 3 products of 4 are enough, and they bring 0.85.
Output columns
products- the category’s products with sales in September
Notes
- Products are ordered by revenue descending, ties by product_id ascending.
Solve it free
Your query runs on PostgreSQL in your browser. No sign-up needed.