What Share of Revenue Does Each Category Own?

Merchandising at Whetcode Supply is presenting a pie chart at the quarterly review, and a pie chart needs slices rather than totals. Three product categories are on sale, and the question is what portion of the shop's money each one is responsible for.

The difficulty is that every slice needs a number that belongs to the whole pie. A category's own revenue can be worked out from its own rows, but the denominator — everything the shop took — comes from all the rows at once, and it has to be available beside each category's figure rather than looked up separately and pasted in.

There is the usual split as well: an order says how many units were bought, and the price and the category are both facts about the product.

orders — one row per order. quantity is units bought. product_id points at a row in products.

products — one row per product. category is the group it is sold under. price is the price of a single unit, in dollars.

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

Task: Write a query returning two columns, category and revenue_share, with one row per category that has sold at least one unit. revenue_share is that category's revenue divided by the revenue of the whole shop — a fraction between 0 and 1, not a percentage, rounded to 4 decimal places. An order's revenue is its quantity multiplied by the unit price of the product ordered. A category that has never sold anything produces no row rather than a row reading 0. Rows come back in alphabetical order of category.

Example output — shape only, on invented categories. The shares always come to 1 between them.