Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Unpivot a wide monthly summary · 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

Unpivot a wide monthly summary

A charting library wants long data: one row per month and metric, with the value in a single column. What it has been handed is the wide monthly summary in the editor, with one column per metric. Return month, metric and amount, where metric is either paid or pending. Order by month, then metric.

Result columns · in this order

`month`	Year and month of the orders, as YYYY-MM.
`metric`	Which measure this row carries: paid or pending.
`amount`	Revenue for that month and metric, to two decimal places.

How to approach it

Compute the wide shape once in a CTE, then write one SELECT per metric column and stack them with UNION ALL.

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.

month	metric	amount
2026-01	paid	400
2026-01	pending	45
2026-02	paid	715
2026-02	pending	85
2026-03	paid	95
2026-03	pending	0

6 rows — all rows shown.

Constraints

1Start from the wide shape in the starter — compute it in a CTE, then reshape it. Recomputing the aggregate once per metric would work and would read as two unrelated queries stapled together.
2There is no UNPIVOT clause here. Use one SELECT per source column, combined with UNION ALL.
3UNION ALL, not UNION. The rows are already distinct by construction, and UNION would pay for a deduplication pass that can only remove rows you wanted.
4The metric name is a literal you write, not a value in the data — that is the part that makes unpivoting the harder direction.
5Every month must appear for every metric, including where the amount is 0.
6Order by month, then metric.

Worked example

The wide row for 2026-01 is paid_total = 400.00, pending_total = 45.00. Unpivoted it becomes two rows: (2026-01, paid, 400.00) and (2026-01, pending, 45.00). Three months and two metrics gives six rows out of three rows in. Pivoting narrows a result and unpivoting widens it, which is why the row count is the first thing to check. The asymmetry is worth naming: pivoting reads column names out of the data, while unpivoting has to write them in as literals. Add a third metric and this query grows another SELECT — there is no way to make it read the column list dynamically in plain SQL.

What this tests

Whether you can reshape in the harder direction without an UNPIVOT clause, and whether you know why UNION ALL rather than UNION.

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