Loadingone moment…
Sign in to run and submit your work
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.
or
CODE WORKSPACE
A report shows revenue per order status and needs a grand-total row underneath. On engines that have it, GROUP BY ROLLUP(status) does this in one clause — and emits the total row with NULL in the status column.
This engine has no ROLLUP. Build the same report by hand, with a total row that a reader can tell apart from a status at a glance. Return grouping_level, orders, revenue and is_total.
Result columns · in this order
grouping_level | The normalised status, or 'all statuses' on the total row. |
orders | Number of orders at this level. |
revenue | Revenue at this level, to two decimal places. |
is_total | 0 for a status row, 1 for the grand-total row. |
How to approach it
Aggregate once into a CTE, then UNION ALL a second SELECT that sums the CTE.
Sample input
| 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
| grouping_level | orders | revenue | is_total |
|---|---|---|---|
| paid | 9 | 1210 | 0 |
| pending | 2 | 130 | 0 |
| refunded | 1 | 90 | 0 |
| all statuses | 12 | 1430 | 1 |
4 rows — all rows shown.
Constraints
all statuses — not NULL. A NULL in that column is indistinguishable from a genuinely missing status, which is the single sharp edge of real ROLLUP.is_total is 0 for a status row and 1 for the total row, so a consumer can filter or style on it without parsing the label.Worked example
Per status the table gives paid 9 orders / 1210.00, pending 2 / 130.00 and refunded 1 / 90.00. The total row is 12 orders and 1430.00, labelled all statuses with is_total = 1.
On a real ROLLUP that last row would come back as (NULL, 12, 1430.00), and the only way to tell it from a row whose status was genuinely NULL is the GROUPING() function. Labelling it explicitly removes the ambiguity before it reaches anybody.
Summing the CTE rather than re-scanning matters too: if the two halves ever disagree, the report shows a total that does not match its own rows, and no error is raised.
What this tests
Whether you can emulate ROLLUP portably, and whether you know the NULL ambiguity that makes the real thing need a GROUPING() call.
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.
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.