Revenue from order lines
Return category and revenue_cents from completed orders, using SUM(quantity × unit_price_cents) from the actual order lines. Do not use current list prices or repeat the whole order total for every line. Row order does not matter.
Output
categoryrevenue_centsquery.sql
DuckDB SQLRestoring your saved draft…
Run a query to see results.
Reveal solution & explanation
SELECT p.category, SUM(i.quantity * i.unit_price_cents) AS revenue_cents FROM order_items i JOIN products p ON p.product_id = i.product_id JOIN orders o ON o.order_id = i.order_id WHERE o.status = 'completed' GROUP BY p.category;
List prices may differ from historical sale prices. Summing order totals after a line join would double-count multi-line orders.
Data & schema
- order_id
- INTEGER · PK
- customer_id
- INTEGER
- ordered_at
- TIMESTAMP
- status
- VARCHAR
- order_total_cents
- INTEGER
Table notes & relationships
One row per order. Only completed orders count as revenue unless a question says otherwise. Times are UTC.
customer_id→ customers.customer_idstatuscompleted / cancelled / pending
Fictional teaching data. Money is USD cents; timestamps are UTC.