Product Revenue Share

The merchandising team at Whetcode Supply suspects the shop is really a one-product business wearing a five-product catalogue, and wants the evidence: each item's share of everything the shop has taken, with the three biggest shares on the page. A share, not an amount — the amounts are already known and they do not settle the argument.

That is the awkward part. Each line needs a figure for its own item and a figure covering every item at once, and the second one is out of view once the rows have been gathered item by item.

orders — one row per order. customer_id is the id of the customer who placed it, product_id the id of the item bought, quantity how many units of that item, and order_date the day it was placed. An order row covers one item only.

products — the catalogue, one row per item on sale. price is what a single unit costs, in dollars.

The tables already exist in the database — there is nothing to create or load.

Task: Write a query returning two columns, name (the product's name) and revenue_pct (that product's revenue as a percentage of the shop's total revenue, rounded to one decimal place), returning only the three largest shares, biggest first. Give a percentage out of 100, not a fraction of 1 — a product holding a tenth of revenue reads 10.0, not 0.1. The third and fourth shares are almost a point apart, so which three come back is not in doubt.

Example output — shape only, on an invented catalogue. The figures are percentages out of 100: an item on 41.8 holds a little over two fifths of the takings: