Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Best selling products, paid only · 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

Best selling products, paid only

Merchandising wants to know which products actually sell, counting only lines that belong to a paid order. shop_orders.status is typed by hand, so its capitalisation and padding vary. Return one row per product that has at least 3 paid lines, ordered by product.

Result columns · in this order

`product`	The product.
`paid_lines`	How many paid lines it appears on.
`paid_revenue`	Total value of those paid lines.

How to approach it

One condition is about a single row, the other is about a whole group. Put each where it can be evaluated.

Sample input

shop_ordersOne row per online order. Note the times of day and the spellings of status.

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. Several lines can share an order_id.

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.

product	paid_lines	paid_revenue
Gadget	4	400
Gizmo	4	300
Widget	7	435

3 rows — all rows shown.

Constraints

1Only lines whose order is paid count. Treat every spelling of paid as paid.
2The 3-line threshold applies to the paid lines, not to all lines of the product.
3A product below the threshold does not appear at all.
4One of these two filters removes rows and the other removes groups — they cannot go in the same clause.

Worked example

Gizmo has 5 lines in total, but one of them is on order 1010, which is 'pending'. Only 4 are paid, so it qualifies and reports paid_lines 4 and paid_revenue 300. Drop the status filter and Gizmo reads 5 lines and 340 — it still passes the threshold, so the row looks plausible while being wrong. Widget shifts from 7 lines to 9 the same way.

What this tests

Which clause a condition belongs in: WHERE removes rows before grouping and can see individual columns; HAVING removes groups after it and can see aggregates.

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