D8LooPFocus modeCODE WORKSPACE
Clean order status CTE
Using a cleanup CTE, map status into clean_status ('complete_or_paid' for paid or shipped, 'open' for pending, otherwise the status itself), then return clean_status and the order count per bucket. Order by orders descending, then clean_status ascending.
First CTE builds clean_status with CASE; the outer query groups and counts it.
Sample input
ordersCASE maps paid/shipped → 'complete_or_paid', pending → 'open', else the status itself.
| order_id | status |
|---|---|
| 1001 | paid |
| 1004 | shipped |
| 1003 | pending |
| 1006 | refunded |
| 1010 | paid |
Expected output
Expected outputCounts per cleaned bucket: paid(6)+shipped(3)=complete_or_paid 9, pending(2)=open, refunded(1). Most first.
| clean_status | orders |
|---|---|
| complete_or_paid | 9 |
| open | 2 |
| refunded | 1 |
Constraints
Do the CASE mapping in one cleanup CTE, then GROUP BY clean_status with COUNT(*) AS orders. Order by orders DESC, clean_status ASC.
Expected skills
Centralising a CASE in a cleanup CTE, then aggregating the cleaned shape.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.