Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Combine online and in-store orders · 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

Combine online and in-store orders

The shop sells online (shop_orders) and in person (store_orders). The two tables number their orders separately, so an id does not identify an order across both. Return one row per customer covering both channels, ordered by customer_id.

Result columns · in this order

`customer_id`	The customer.
`orders`	Total orders across both channels.
`revenue`	Total value across both channels.

How to approach it

Stack the two tables first, then aggregate the stack. The operator you choose decides whether rows survive.

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.

store_ordersOne row per in-store sale. Same shape as shop_orders, but dated to the day.

order_id	customer_id	sold_on	status	order_total
2001	c1	2026-01-05	paid	120
2002	c3	2026-02-05	paid	40
2003	c5	2026-02-18	paid	75
2004	c2	2026-03-01	pending	55
2005	c4	2026-03-04	paid	65
2006	c1	2026-03-06	paid	20
2007	c3	2026-03-10	paid	90
2008	c2	2026-03-12	paid	80

8 rows — all rows shown.

Expected output

The exact result your query should return for this input.

customer_id	orders	revenue
c1	5	605
c2	5	370
c3	4	420
c4	3	300
c5	3	280

5 rows — all rows shown.

Constraints

1Every order in both tables counts, including orders of the same value by the same customer.
2Order status is irrelevant here — count them all.
3The two tables have different column names for the date, so select only what you need.
4orders must equal the total number of rows in both tables for that customer.

Worked example

Customer c1 has three online orders (120, 45, 300) and two in store (120, 20) — 5 orders totalling 605. The 120 online and the 120 in store are different sales on different days. Stack the tables with UNION and SQL removes one of them as a duplicate row, reporting 4 orders and 485. UNION ALL keeps both, because it never compares rows at all.

What this tests

The difference between UNION and UNION ALL: that UNION deduplicates the combined result, which silently destroys real rows once the identifying columns are dropped.

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