Percent of Total

A favorite interview question: what share of the total revenue does each category bring? A share is a group's own total divided by the grand total, times 100. The catch: after GROUP BY, each row only sees its own group, so the grand total isn't in the row.

The idea in one sentence: a window with empty brackets, SUM(SUM(x)) OVER (), runs after the grouping, adds up the group totals of all the rows and puts that grand total on every row. For example, on a scores table with one row per game, each team's share of all the points:

SELECT team, SUM(points) AS points,
    SUM(points) * 100.0 / SUM(SUM(points)) OVER () AS share
FROM scores
GROUP BY team

A team with 30 of the 50 points gets 60.0. Mind the 100.0: in SQLite a whole number divided by a whole number is a whole number, so 30 / 50 gives 0. And ROUND(x, 1) keeps one decimal.

In the shop, the revenue of an order line is quantity * unit_price in order_items, and its category is in products. Your query prints:

category | revenue | percent
kitchen | 128 | 46.5
office | 74 | 26.9
home | 73 | 26.5
(3 rows)

Your task: in query, list each category with its revenue and its percent of all the revenue, rounded to 1 decimal, biggest share first.

Assignment
Follow the Coding Tutorial to build the query one clause per line: the SELECT, the percent, FROM, the JOIN, GROUP BY and ORDER BY.

Hint
The grand total is SUM(SUM(quantity * unit_price)) OVER (): the inner SUM is the category's revenue, and the empty OVER () adds up all the categories. Multiply by 100.0, not 100, before you divide.