Two metrics per category in one pivot

A single row with two metrics per category — revenue (qty * unit_price) and total quantity: electronics_rev, electronics_qty, books_rev, books_qty, clothing_rev, clothing_qty. Join order_items to products for the category.

Columns

order_items4 columns

columntype

order_idVARCHAR

product_idVARCHAR

qtyBIGINT

unit_priceBIGINT

products3 columns

columntype

product_idVARCHAR

categoryVARCHAR

list_priceBIGINT

The tables

order_itemsfirst 5 of 11 rows

order_idproduct_idqtyunit_price

o_001p_003320

o_001p_005140

o_002p_0011200

o_003p_003120

o_003p_004130

productsfirst 5 of 6 rows

product_idcategorylist_price

p_001electronics500

p_002electronics150

p_003books20

p_004books35

p_005clothing45

If you get stuck

Show hint · 1 hint Walkthrough Reveal answer

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows