Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Returns per order, including none · 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

Returns per order, including none

Every order should appear in this report, whether or not anything was returned against it. Return one row per order with how many returns it has, ordered by order_id.

Result columns · in this order

`order_id`	The order.
`order_total`	Its value.
`returns`	How many returns were logged against it.

How to approach it

A LEFT JOIN invents a row of NULLs for every unmatched order. Count something that is NULL on those rows.

Sample input

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_returnsOne row per returned item. order_id is NULL when the return was never matched.

return_id	order_id	reason	refund_amount	returned_on
1	1001	damaged	60	2026-01-09
2	1004	wrong size	50	2026-02-05
3	1004	wrong size	50	2026-02-20
4	1007	changed mind	90	2026-03-01
5	null	unlinked	25	2026-02-11
6	1008	damaged	150	2026-02-16
7	1011	late delivery	130	2026-03-20
8	1002	damaged	80	2026-01-15
9	1002	damaged	80	2026-01-18
10	1008	damaged	150	2026-02-20

10 rows — all rows shown.

Expected output

The exact result your query should return for this input.

order_id	order_total	returns
1001	120	1
1002	80	2
1003	45	0
1004	200	2
1005	60	0
1006	150	0
1007	90	1
1008	300	2
1009	75	0
1010	85	0
1011	130	1
1012	95	0

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

Constraints

1All twelve orders appear, including the six with no returns.
2An order with no returns reports 0, not 1 and not an empty cell.
3An order returned twice reports 2.
4The unmatched return row (order_id = NULL) belongs to no order and must not be counted against any of them.

Worked example

Order 1003 has no returns. A LEFT JOIN still produces one row for it, with every shop_returns column set to NULL. COUNT(*) counts that row and reports 1 return for an order that has none. COUNT(r.return_id) skips NULLs and correctly reports 0 — the same distinction, with opposite consequences, as COUNT(*) versus COUNT(column) on a plain table.

What this tests

What a LEFT JOIN produces for an unmatched row, and why COUNT(*) and COUNT(column) disagree on exactly those rows.

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