Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Every order against its customer's average · 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

Every order against its customer's average

Account managers want to see every order next to that customer's typical order, so an unusually large or small one is obvious without flipping between two reports. Return one row per order — all twelve of them — ordered by customer_id, then order_id.

Result columns · in this order

`order_id`	The order.
`customer_id`	Who placed it.
`order_total`	What this order was worth.
`customer_avg`	The average order value for that customer.
`diff_from_avg`	How far this order sits from that average.

How to approach it

Read the row count in the contract first — it rules out one whole approach.

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.

Expected output

The exact result your query should return for this input.

order_id	customer_id	order_total	customer_avg	diff_from_avg
1001	c1	120	155	-35
1003	c1	45	155	-110
1008	c1	300	155	145
1002	c2	80	78.33	1.67
1005	c2	60	78.33	-18.33
1012	c2	95	78.33	16.67
1004	c3	200	145	55
1007	c3	90	145	-55
1006	c4	150	117.5	32.5
1010	c4	85	117.5	-32.5
1009	c5	75	102.5	-27.5
1011	c5	130	102.5	27.5

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

Constraints

1Every order appears. This report annotates orders; it does not summarise them away.
2customer_avg is the average order_total across that customer's orders, repeated on each of their rows.
3diff_from_avg is order_total minus customer_avg, so it is negative for below-average orders and the values within one customer sum to zero.
4Round both derived columns to 2 decimal places.
5Status is irrelevant; every order counts.
6Order by customer_id, then order_id.

Worked example

c1 placed orders of 45, 120 and 300, averaging 155. Their three rows therefore read -110.00, -35.00 and +145.00, which sum to zero — a useful check that the average really is the customer's own. GROUP BY customer_id would produce the same 155, but only one row for the whole customer. The individual orders, which are the entire point of the report, would be gone.

What this tests

That a window function computes an aggregate without collapsing rows — the difference between annotating a dataset and summarising it, which is the reason window functions exist.

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