Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Band refunds by size · 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

Band refunds by size

Finance groups refunds into three sizes: large is 100 or more, medium is 50 up to but not including 100, and small is anything below 50. Return one row per band, ordered by band.

Result columns · in this order

`band`	The size band: large, medium or small.
`returns`	How many returns fall in it.
`refunded`	Total refunded in that band.

How to approach it

Order the arms so the narrowest condition is tested first.

Sample input

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.

band	returns	refunded
large	3	430
medium	6	410
small	1	25

3 rows — all rows shown.

Constraints

1Every return falls into exactly one band, and all three bands appear.
2The bands share their boundaries: a 150.00 refund satisfies both 'at least 100' and 'at least 50'.
3CASE stops at the first arm that matches, so the arm order decides which band a refund lands in.
4Group by the band, not by the raw amount.

Worked example

A 150.00 refund is large. Written the other way round — WHEN refund_amount >= 50 THEN 'medium' first — that refund matches the 50 arm and is labelled medium. The result is not merely a few rows misfiled. The large arm becomes unreachable, so the band disappears from the output entirely: 9 medium and 1 small, with no sign that anything went wrong.

What this tests

That CASE returns the first matching arm rather than the best one, so overlapping conditions have to be ordered from the most specific to the least.

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