Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Join three tables into an order detail · 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

Join three tables into an order detail

Support needs a line-by-line view of what one customer bought: their name, the order, and each product on it. Return every line belonging to customer c4, ordered by order_id then item_id.

Result columns · in this order

`full_name`	The customer.
`order_id`	The order the line belongs to.
`product`	Product on the line.
`line_amount`	Value of the line.

How to approach it

Add one table at a time and check the row count after each join.

Sample input

shop_customersOne row per customer. region is NULL when unassigned.

customer_id	full_name	region	signed_up_on
c1	Ada Okafor	North	2025-11-02
c2	Bo Lindqvist	null	2025-12-14
c3	Chen Wei	South	2026-01-03
c4	Dara O'Neill	North	2026-01-20
c5	Eve Marsh	null	2026-02-01

5 rows — all rows shown.

shop_ordersOne row per online order.

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.

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.

full_name	order_id	product	line_amount
Dara O'Neill	1006	Gizmo	50
Dara O'Neill	1006	Gizmo	50
Dara O'Neill	1006	Gizmo	50
Dara O'Neill	1010	Widget	45
Dara O'Neill	1010	Gizmo	40

5 rows — all rows shown.

Constraints

1Three tables are involved and each join uses a different key: customers to orders on customer_id, orders to items on order_id.
2One row per line item, not per order — a three-line order produces three rows.
3Alias the tables; full_name, order_id and product come from three different places.
4Only customer c4 appears.

Worked example

c4 has two orders: 1006 with three Gizmo lines and 1010 with two lines. The result is five rows, and full_name repeats on every one of them. That repetition is correct, not a bug. The result is at line grain, so anything describing the customer or the order is repeated across the lines it covers — the same fan-out that makes summing an order-level column across this join wrong.

What this tests

Chaining joins across different keys, and reading the grain of a multi-table result rather than assuming it matches the first table listed.

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