Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Return rate per customer · 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

Return rate per customer

Operations wants a return rate per customer: of the orders they placed, what share came back. shop_returns holds one row per returned item, so a single order can appear in it more than once, and some return rows were never matched to an order at all. Return one row per customer, ordered by customer_id.

Result columns · in this order

`customer_id`	The customer.
`orders`	Orders they placed.
`returned_orders`	How many of those orders came back, counted once each.
`return_rate_pct`	Returned orders as a percentage of orders placed.

How to approach it

Reduce the returns to one row per order before you join anything to the orders table.

Sample input

shop_ordersOne row per online order. status is entered by hand and its spelling varies.

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 to an order.

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.

customer_id	orders	returned_orders	return_rate_pct
c1	3	2	66.7
c2	3	1	33.3
c3	2	2	100
c4	2	0	0
c5	2	1	50

5 rows — all rows shown.

Constraints

1orders counts the customer's orders in shop_orders.
2returned_orders counts their distinct orders that appear in shop_returns. Order 1004 has two return rows and must count once.
3Return rows whose order_id is NULL belong to no order and must not be counted anywhere.
4A customer with no returns still appears, with 0 and a rate of 0.0 — c4 is that customer.
5return_rate_pct is returned_orders over orders as a percentage, rounded to 1 decimal place.
6Order by customer_id.

Worked example

c3 placed 2 orders, 1004 and 1007. Order 1004 has two return rows and 1007 has one, so there are 3 return rows across 2 returned orders — a rate of 100.0. Join shop_orders straight to shop_returns and count rows instead, and c3 reports 3 returned orders against 2 placed: a return rate of 150%. The join multiplied the orders, and any rate above 100 is the signature of exactly that bug.

What this tests

Collapsing a one-to-many side to distinct keys before joining, so a rate cannot exceed 100%, together with the LEFT JOIN that keeps a customer who has nothing to report.

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