Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Revenue per customer without double counting · SQL scenario · D8LooP

Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.

CODE WORKSPACE

Revenue per customer without double counting

Finance wants a per-customer summary from shop_orders and shop_order_items. An order can have several lines, and the lines on an order always sum back to that order's order_total. Return one row per customer, ordered by customer_id.

Result columns · in this order

`customer_id`	The customer.
`orders`	How many distinct orders they placed.
`items`	How many line items across those orders.
`revenue`	Their true total spend.

How to approach it

Join first, then look at how many rows each order produced before you choose what to total.

Sample input

shop_ordersOne row per online order. Note the times of day and the spellings of status.

order_id	customer_id	placed_at	status	order_total
1001	c1	2026-01-05 10:00:00	paid	120
1002	c2	2026-01-12 14:30:00	Paid	80
1003	c1	2026-01-20 09:15:00	pending	45
1004	c3	2026-01-31 23:30:00	PAID	200
1005	c2	2026-02-01 00:15:00	paid	60
1006	c4	2026-02-03 11:00:00	paid	150
1007	c3	2026-02-10 16:45:00	refunded	90
1008	c1	2026-02-14 12:00:00	paid	300
1009	c5	2026-02-18 08:30:00	Paid	75
1010	c4	2026-02-25 19:20:00	pending	85
1011	c5	2026-02-28 21:00:00	paid	130
1012	c2	2026-03-02 10:10:00	paid	95

12 rows — scroll inside the table to see them all.

shop_order_itemsOne row per line on an order. Several lines can share an order_id.

item_id	order_id	product	quantity	line_amount
1	1001	Widget	1	60
2	1001	Gadget	1	60
3	1002	Widget	1	80
4	1003	Doohickey	1	45
5	1004	Widget	1	50
6	1004	Widget	1	50
7	1004	Widget	1	50
8	1004	Widget	1	50
9	1005	Gadget	1	60
10	1006	Gizmo	1	50
11	1006	Gizmo	1	50
12	1006	Gizmo	1	50
13	1007	Widget	1	90
14	1008	Gizmo	1	150
15	1008	Gadget	1	150
16	1009	Doohickey	1	75
17	1010	Widget	1	45
18	1010	Gizmo	1	40
19	1011	Gadget	1	130
20	1012	Widget	1	95

20 rows — scroll inside the table to see them all.

Expected output

The exact result your query should return for this input.

customer_id	orders	items	revenue
c1	3	5	465
c2	3	3	235
c3	2	5	290
c4	2	5	235
c5	2	2	205

5 rows — all rows shown.

Constraints

1Joining orders to their lines multiplies each order row by its number of lines. Order 1004 has four lines, so it appears four times.
2revenue must be each customer's true spend. Totalling order_total across the joined rows counts a multi-line order once per line.
3orders counts orders, not joined rows.
4Every customer has at least one line, so no customer disappears.

Worked example

Customer c3 placed 2 orders worth 200 and 90, so revenue is 290. Order 1004 has four lines, so the join produces 5 rows for c3. SUM(o.order_total) over those 5 rows gives 890 — it adds the 200 four times. SUM(i.line_amount) gives 290, because the lines are already the parts of the total.

What this tests

Reasoning about grain across a join: recognising that a one-to-many join changes what a row means, and choosing the column that is correct at the joined grain.

SQL

AI evaluation

The reviewer reads your query.

Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.

Desktop only

This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.

Read the library instead Back to modules