Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Orders and revenue by month · 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

Line items per order

Return o.order_id and line_items (the count of matching order_items rows) for every order that has at least one line item. Order by order_id.

Result columns · in this order

1order_id
2line_items

How to approach it

JOIN order_items ON oi.order_id = o.order_id, GROUP BY o.order_id, COUNT(*) AS line_items.

Sample input

ordersOrders (order_id shown). Orders with no items are excluded by the INNER JOIN.

order_id
1001
1002
1003
1004

4 rows — all rows shown.

order_itemsItem lines; order_id repeats once per line item (15 rows in the sandbox).

order_id	sku
1001	SKU-A
1001	SKU-B
1001	SKU-C
1002	SKU-C
1002	SKU-D
1004	SKU-A

6 rows — all rows shown.

Expected output

One row per order that has items, with its line-item count. 7 of the 12 orders have items.

order_id	line_items
1001	3
1002	2
1004	3
1005	2
1007	1
1008	2
1010	2

7 rows — all rows shown.

Constraints

JOIN orders to order_items on order_id (an INNER JOIN, so orders with no items are excluded). GROUP BY o.order_id and COUNT(*) the line items, aliased as line_items.

Expected skills

Joining to a finer-grain table and aggregating back to one row per order with GROUP BY.

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