Each category's share of revenue
Hard
Show each category, its revenue and share_pct: its percentage of all revenue, rounded to 1 decimal.
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Your query✔ Solved
Show the expected result
| category | revenue | share_pct |
|---|---|---|
| Books | 2638.0 | 11.2 |
| Electronics | 11502.0 | 49.0 |
| Furniture | 8153.0 | 34.8 |
| Grocery | 824.0 | 3.5 |
| Stationery | 339.0 | 1.4 |
5 rows
Show a solution
WITH r AS (SELECT p.category, SUM(i.quantity * i.unit_price) AS revenue FROM order_items i JOIN products p ON p.product_id = i.product_id GROUP BY p.category) SELECT category, revenue, ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS share_pct FROM r;
SQL used
New to SQL? The free SQL course teaches everything these problems use, with a self-checking exercise in every lesson.