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
shop_orders.status is typed by hand at the till, so the same status appears with different capitalisation and stray spaces. Every spelling that reads as paid means paid.
Return one row per customer who has at least one paid order, ordered by customer_id.
Result columns · in this order
customer_id | The customer. |
paid_orders | How many of their orders are paid. |
paid_revenue | Total value of those paid orders. |
How to approach it
Normalise the column in the WHERE clause so one comparison covers every spelling.
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
| customer_id | paid_orders | paid_revenue |
|---|---|---|
| c1 | 2 | 420 |
| c2 | 3 | 235 |
| c3 | 1 | 200 |
| c4 | 1 | 150 |
| c5 | 2 | 205 |
5 rows — all rows shown.
Constraints
'paid', 'Paid', 'PAID' and ' paid ' as the same status.'pending' and 'refunded' are not paid and must not be counted.IN list only works until someone types a seventh spelling.Worked example
Customer c2 has three orders: 'Paid', 'paid' and 'paid'. All three count, so paid_orders is 3 and paid_revenue is 235.
Filtering with status = 'paid' matches only the two lowercase ones and reports 155. c3, whose single paid order is written 'PAID', disappears from the result entirely.
What this tests
Defensive filtering on hand-entered text: normalising case and whitespace before comparing, rather than trusting that a column holds one canonical spelling.
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.