Sales Report by Category
What you're building
The classic first analytics query: a revenue summary grouped by category β the backbone of every sales dashboard.
Requirements
Join orders to products and return, for each category:
categoryβ the product categorytotal_revenueβSUM(price * quantity)across all orders in that categoryunits_soldβSUM(quantity)
Order results by total_revenue descending.
Tables: products(id, name, category, price) Β· orders(id, customer_id, product_id, quantity, order_date)
Stretch goals
- Add a
ROUND(total_revenue, 2)to tidy the output. - Filter to a single month using
WHERE order_date LIKE '2024-01%'.
What this proves
Every analytics interview includes a GROUP BY + JOIN question. This is the canonical form.
β‘ 40 XP
query.sqlRuns in your browser (SQLite/WASM)
Loading editorβ¦
Tests1 to pass
Revenue and units per category, high to low
Run your code to check these.