lesson 21 Joins & sets · intermediate

Revenue across tables

Calculate revenue while joining orders and related records.

the idea

Join transaction rows to product prices, multiply price by quantity, then aggregate at the desired grain. Filter status before computing paid revenue.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

customers4 columns · 8 rows

idinteger · key	nametext	citytext	joined_attext
1	Alice	Chicago	2024-01-10
2	Ben	Austin	2024-02-12
3	Cara	Seattle	2024-03-05
4	Dev	Chicago	2024-04-20
5	Elena	NULL	2024-05-01

showing 5 of 8 rows

products4 columns · 6 rows

idinteger · key	nametext	categorytext	pricereal
1	Notebook	Stationery	12
2	Desk lamp	Home	45
3	Keyboard	Tech	80
4	Pen set	Stationery	8
5	Headphones	Tech	120

showing 5 of 6 rows

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT c.name, SUM(o.quantity * p.price) AS revenue
FROM orders o
JOIN customers c ON c.id=o.customer_id
JOIN products p ON p.id=o.product_id
WHERE o.status='paid'
GROUP BY c.id,c.name;

Watch for: Summing price without quantity undercounts multi-unit orders.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Read an order line with its price. Join one order line to its product so quantity and price sit on the same row. The next step multiplies them and aggregates paid revenue.
step-1.sql
sql›
```
SELECT o.id, o.quantity, p.price, o.status
FROM orders o
JOIN products p ON p.id = o.product_id
LIMIT 8;
```
2
Run the reference. Join transaction rows to product prices, multiply price by quantity, then aggregate at the desired grain. Filter status before computing paid revenue.
step-2.sql
sql›
```
SELECT c.name, SUM(o.quantity * p.price) AS revenue
FROM orders o
JOIN customers c ON c.id=o.customer_id
JOIN products p ON p.id=o.product_id
WHERE o.status='paid'
GROUP BY c.id,c.name;
```