Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Each customer's top-selling product · 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

Each customer's top-selling product

Merchandising wants each customer's strongest product line — the single product they have spent the most on across their paid orders. Revenue lives on shop_order_items.line_amount, and status in shop_orders is typed by hand, so its spelling varies. Return one row per customer, ordered by customer_id.

Result columns · in this order

`customer_id`	The customer.
`product`	The product they spent the most on.
`revenue`	What they spent on it across paid orders.

How to approach it

Aggregate to one row per customer and product first — that pair is the thing being ranked.

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_order_itemsOne row per line on an order. An order can have several lines.

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	product	revenue
c1	Gadget	210
c2	Widget	175
c3	Widget	200
c4	Gizmo	150
c5	Gadget	130

5 rows — all rows shown.

Constraints

1Only paid orders count. status appears as 'paid', 'Paid', 'PAID' and ' paid ' — normalise it rather than listing the spellings you can see today.
2revenue is the sum of line_amount for that customer and product. Never sum order_total across the item join: an order with four lines would be counted four times.
3Exactly one row per customer — their single highest-revenue product.
4Break a tie on product alphabetically so the result is deterministic.
5Every customer with at least one paid order appears.
6Order by customer_id.

Worked example

c1's paid orders are 1001 (Widget 60, Gadget 60) and 1008 (Gizmo 150, Gadget 150). Gadget totals 210, Gizmo 150 and Widget 60, so c1's row is Gadget at 210. MAX(revenue) grouped by customer would return 210 but would not say which product produced it — and putting product beside a bare MAX returns whichever row SQLite happened to keep, which is not necessarily the winning one.

What this tests

Top-one-per-group with ROW_NUMBER, composed with a fan-out-safe sum and a normalised status filter — three Foundations moves that have to hold at once in a single query.

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