Share of revenue by category

Medium

Question

The sales table lists sales of an online shop, with the product category and the revenue of each sale.

For every category return category, revenue (the total revenue of the category) and share_pct (the percentage of the revenue of ALL categories, rounded to 1 decimal).

Sort by revenue from high to low, then by category.

The order of the rows matters for this question.

window function
percent of total
sum over
group by
integer division

Read first: Lesson 47, SQL window functions: ranks, running totals, LAG and frames

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

sales4 columns · 9 rowsnot run yet

CREATE TABLE sales (id INTEGER PRIMARY KEY, category TEXT NOT NULL, product TEXT NOT NULL, revenue INTEGER NOT NULL);

Example rows of the sales table
id	category	product	revenue
1	Grocery	Basmati Rice 5kg	15000
2	Electronics	Earbuds	18000
3	Fashion	Kurta	9000
4	Grocery	Cooking Oil	12000
5	Home	Mixer Grinder	6000
6	Electronics	Power Bank	5500
7	Grocery	Tea Pack	14000
8	Fashion	Jeans	6500
9	Home	Cushion Covers	4000

About this SQL

A window function runs after GROUP BY, so SUM(SUM(revenue)) OVER () means "the sum of the group sums". An empty OVER () uses the whole result as one window. Whole number divided by whole number is a whole number in SQLite, so use 100.0.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready