HardSQL challenge #217WINDOW FUNCTIONPERCENTAGEGROUP BY

Calculate Each Category's Share of Total Revenue

Return every product category with the percentage of total revenue it accounts for.

The challenge runs on the SQLabHub practice database (SQLite) and uses the products and order_items tables. You can solve it in the browser, with nothing to install.

Solve This SQL Challenge

The task

Find percentage revenue per category.

A correct answer returns 10 rows with 2 columns: category, pct.

Tables used in this challenge

products 120 rows

id INTEGER (PK) · name TEXT · category TEXT · price REAL · cost REAL · stock INTEGER · sku TEXT · status TEXT · rating REAL · review_count INTEGER

First 3 rows of the products table
idnamecategorypricecoststockskustatusratingreview_count
1Play Kitchen DeluxeToys1786.38589.51257SKU-000001active457
2Micro-Cut Paper Shredder 8-SheetOffice Supplies907.93281.46131SKU-000002active2.5447
34K Dual Dash CameraAutomotive568.89204.8176SKU-000003active4.129

order_items 496 rows

id INTEGER (PK) · order_id INTEGER · product_id INTEGER · quantity INTEGER · unit_price REAL · subtotal REAL

First 3 rows of the order_items table
idorder_idproduct_idquantityunit_pricesubtotal
11765192.21961.05
21823107.1321.3
316515.395.39

The first rows of each table are shown. See how all the tables relate on the database schema page.

Expected output

Expected output of the challenge
categorypct
Automotive11.739186671748131
Books11.056681747285458
Clothing7.638494190570912
Electronics7.037413374416919
Food & Beverage11.574683471413142
Health & Beauty9.056921569967178
Home & Garden8.473909249853955
Office Supplies12.946163297870514
Sports & Outdoors8.266501891725458
Toys12.210044535148338

The complete result: 10 rows. This is the output of the model solution on the practice database. The task does not fix a sort order, so your rows may come back in a different order.

What you practise

  • Summarising many rows into one value with COUNT, SUM, AVG, MIN or MAX
  • Grouping rows with GROUP BY to get one result row per group
  • Combining rows from two tables on a matching key with an inner join
  • Attaching a total or average to every row with an aggregate OVER ()
  • Deriving a new column from an expression and naming it with an alias

Hint and approach

Try the challenge first. Open these only when you need them.

Show the hint

SUM OVER total

Show the step-by-step approach

How the model solution is built, in the order the database evaluates it:

  1. Read from the products table.
  2. Join order_items on products.id = order_items.product_id.
  3. Group the rows by products.category, so the result has one row for each value.
  4. For each group, compute SUM(order_items.subtotal).
  5. Compute SUM(SUM(order_items.subtotal)) with an empty OVER (), so the figure for the whole result is repeated on every row.
  6. Combine those values into pct: SUM(order_items.subtotal) * 100.0 / SUM(SUM(order_items.subtotal)) OVER ().

Model solution

Checking your session…

What to watch for

SUM(SUM(oi.subtotal)) OVER () is two different sums: the inner one is the GROUP BY aggregate per category and the outer one is a window function adding those category totals together.

  • Window aggregates. A window aggregate keeps every row, while a plain aggregate collapses them. Mixing the two in one SELECT without GROUP BY is an error.
  • INNER JOIN. An inner join drops rows that have no partner on the other side, and it repeats a row once for every match, which inflates SUM and COUNT.
  • GROUP BY. Every selected column must either be in GROUP BY or sit inside an aggregate. SQLite accepts other columns and returns an arbitrary value for them; most databases raise an error.
Solve This SQL Challenge

Same technique, other tables

More practice on this pattern with different columns:

Keep learning