EasyE-commerce12 min

Order line totals

Loading the editor…

Order line totals

Challenge

Attach each order line to its product and compute the line total.

Business context

Support is auditing a tax export. They need the product name and the extended price stored on the line, not today's catalog price.

Instructions

Return order_item_id, product_name, quantity, and line_total_cents (quantity times unit_price_cents). Include every order line. Sort by order_item_id.

Schema

customers

id INTEGER · primary key
name TEXT
email TEXT
city TEXT
country TEXT
created_at TEXT · YYYY-MM-DD

products

id INTEGER · primary key
name TEXT
category TEXT
price_cents INTEGER
stock INTEGER · nullable

orders

id INTEGER · primary key
customer_id INTEGER · customers.id
status TEXT · paid, shipped, pending, cancelled
ordered_at TEXT
shipped_at TEXT · nullable

order_items

id INTEGER · primary key
order_id INTEGER
product_id INTEGER
quantity INTEGER
unit_price_cents INTEGER

payments

id INTEGER · primary key
order_id INTEGER
method TEXT
amount_cents INTEGER
paid_at TEXT · nullable
status TEXT · succeeded, failed, refunded

Sample rows

customers
id	name	email	city	country	created_at
1	Alice Chen	alice@northwind.io	Berlin	Germany	2023-01-12
2	Ben Okonkwo	ben@okonkwo.co	Lagos	Nigeria	2023-03-02
3	Clara Singh	clara@singh.dev	London	United Kingdom	2022-11-20

products
id	name	category	price_cents	stock
1	Trail Bottle	Outdoors	2400	120
2	Desk Lamp	Home	4250	40
3	Noise-canceling Headphones	Electronics	18900	15

orders
id	customer_id	status	ordered_at	shipped_at
1	1	paid	2024-11-02	2024-11-04
2	1	paid	2024-12-14	2024-12-16
3	3	shipped	2025-01-09	2025-01-11

order_items
id	order_id	product_id	quantity	unit_price_cents
1	1	1	2	2400
2	1	4	1	1200
3	2	3	1	18900

payments
id	order_id	method	amount_cents	paid_at	status
1	1	card	6000	2024-11-02	succeeded
2	2	card	18900	2024-12-14	succeeded
3	3	paypal	22000	2025-01-09	succeeded

Draft saves automatically

Ctrl or Cmd + Enter runs the query. The database resets on every run.

Run the query to see rows from the sample database.

Next exercise: Revenue by category