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
Growth wants to know how much of each month's business comes from customers ordering for the very first time, and how much from customers coming back. shop_orders has no column saying which is which — the sequence has to be derived from placed_at.
Return one row per month, ordered by order_month.
Result columns · in this order
order_month | Month as YYYY-MM. |
orders | All orders placed that month. |
new_customer_orders | How many were a customer's first-ever order. |
repeat_orders | How many came from returning customers. |
new_revenue | Revenue from those first-ever orders. |
repeat_revenue | Revenue from returning customers. |
How to approach it
Number each customer's orders across their whole history first, then group by month.
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
| order_month | orders | new_customer_orders | repeat_orders | new_revenue | repeat_revenue |
|---|---|---|---|---|---|
| 2026-01 | 4 | 3 | 1 | 400 | 45 |
| 2026-02 | 7 | 2 | 5 | 225 | 665 |
| 2026-03 | 1 | 0 | 1 | 0 | 95 |
3 rows — all rows shown.
Constraints
placed_at. Every customer has exactly one.c1's January first order stays their first order no matter how the report is sliced.order_month is the YYYY-MM prefix of placed_at.order_total the same way, so new_revenue plus repeat_revenue equals that month's total revenue.order_month.Worked example
In February, c4 and c5 place their first-ever orders (150 and 75) while c1, c2, c3 and c4 come back — 7 orders in total, 2 new and 5 repeat, 225 against 665.
Derive 'first' within each month instead and February changes completely: c1's February order becomes their first order of February and counts as new, inflating acquisition with customers you already had.
What this tests
Deriving a row's position in a history with ROW_NUMBER, then folding that derived flag into conditional aggregation at a completely different grain.
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.