Procurement & Sourcing Analytics · Mission 6 of 30Easy

Spend by category

GROUP BY with SUM aggregation

Back to Procurement & Sourcing Analytics

The Brief

Maya ChenProcurement Directorprocurement-ops

Roll up total invoice spend by category. Join `fact_invoices` to `fact_purchase_orders` on po_id, then to `dim_categories`. Show category_name, total_spend descending.

You'll practice

GROUP BYJOINSUM

Tables & columns available

fact_invoicesfact7 columns

Column	Type	Key
invoice_id	INT	PK
po_id	INT	FK → fact_purchase_orders
supplier_id	INT	FK → dim_suppliers
invoice_date	TEXT
invoice_amount	REAL
po_amount	REAL
payment_date	TEXT

fact_purchase_ordersfact15 columns

Column	Type	Key
po_id	INT	PK
supplier_id	INT	FK → dim_suppliers
category_id	INT	FK → dim_categories
contract_id	INT	FK → dim_contracts
req_date	TEXT
po_date	TEXT
ack_date	TEXT
asn_date	TEXT
gr_date	TEXT
invoice_date	TEXT
qty_ordered	INT
qty_received	INT
unit_price	REAL
contracted_price	REAL
status	TEXT

dim_categoriesdim4 columns

Column	Type	Key
category_id	INT	PK
category_name	TEXT
category_manager	TEXT
spend_type	TEXT

Hints (3)

Each hint you reveal reduces the XP you can earn. Try the query first.

Hint 1

Spend lives on invoices but category lives on the PO header — bridge them through purchase orders.

Hint 2

JOIN to dim_categories on category_id.

Hint 3

GROUP BY category_name, sum invoice_amount, ORDER BY DESC.

← Previous

Mission 5

Mission 7