Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Distinct products on each order · 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

Distinct products on each order

Merchandising wants to know whether large orders are big baskets or repeats of one thing. shop_order_items has one row per line, and the same product can appear on several lines of one order. Return one row per order, ordered by order_id.

Result columns · in this order

`order_id`	The order.
`products`	How many different products it contains.
`lines`	How many line rows it has.

How to approach it

Both numbers come from the same group; only one of them needs the duplicates removed.

Sample input

shop_order_itemsOne row per line on an order.

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.

order_id	products	lines
1001	2	2
1002	1	1
1003	1	1
1004	1	4
1005	1	1
1006	1	3
1007	1	1
1008	2	2
1009	1	1
1010	2	2
1011	1	1
1012	1	1

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

Constraints

1products counts different products; lines counts rows.
2An order with four lines of the same product reports 1 product and 4 lines.
3Every order that has lines appears.
4DISTINCT inside COUNT applies to one column — it is not the same as SELECT DISTINCT.

Worked example

Order 1004 has four Widget lines. It should report products 1 and lines 4. SELECT DISTINCT order_id, product would not help: DISTINCT there applies to the whole selected row, so it deduplicates (order, product) pairs rather than counting products within an order. The distinctness has to live inside the aggregate.

What this tests

That DISTINCT inside an aggregate applies to one column while SELECT DISTINCT applies to the entire row, and that the two answer different questions.

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