Layout

Revenue from order lines

PROBLEM 10 / 24 · BASICAll problems ↗

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.

Outputcategoryrevenue_cents

query.sql

DuckDB SQL

Restoring your saved draft…

⌘ / Ctrl + Enter to run

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_id
  • status completed / cancelled / pending

Fictional teaching data. Money is USD cents; timestamps are UTC.