Apply ordered decision rules to orders
Your task
Return id and route for every shop order. Assign review to refunded orders, closed to cancelled orders, priority to completed orders of at least 3000 cents, and standard to the rest. Sort by ID.
CASE returns the result from the first condition that matches.
Sample data
shop_orders| id | customer_id | ordered_on | channel | status | total_cents |
|---|---|---|---|---|---|
| 201 | 1 | 2025-01-04 | online | completed | 2500 |
| 202 | 2 | 2025-01-10 | store | completed | 1800 |
| 203 | 2 | 2025-01-10 | store | completed | 3200 |
| 204 | 3 | 2025-01-18 | marketplace | refunded | 5000 |
| 205 | 4 | 2025-02-02 | store | completed | 2700 |
| 206 | 1 | 2025-02-04 | online | completed | 4000 |
| 207 | 5 | 2025-02-18 | online | cancelled | 2400 |
| 208 | 6 | 2025-02-28 | online | completed | 1700 |
| 209 | 4 | 2025-03-16 | online | completed | 2900 |
| 210 | 8 | 2025-03-09 | store | completed | 3100 |
Expected output
| id | route |
|---|---|
| 201 | standard |
| 202 | standard |
| 203 | priority |
| 204 | review |
| 205 | standard |
| 206 | priority |
| 207 | closed |
| 208 | standard |
| 209 | standard |
| 210 | priority |
Show a hint
Handle the two exceptional statuses first, then test the amount.
Expressions and missing values guide
Calculate a value for each row
Expressions can combine columns, constants, and functions. This query estimates how much stock is worth at the listed price. AS gives the calculation a name in the result.
SELECT title, price * stock AS stock_value
FROM albums;Build a category from conditions
CASE tests branches from top to bottom. Put the most specific test first: zero would also satisfy stock < 5. ELSE catches the remaining rows.
SELECT title,
CASE
WHEN stock = 0 THEN 'out of stock'
WHEN stock < 5 THEN 'low stock'
ELSE 'stocked'
END AS availability
FROM albums;Account for missing values
NULL represents an unknown or missing value. Compare it with IS NULL rather than equality. NULLIF can also turn a known sentinel into NULL. AVG ignores NULL, so these two averages use different sets of albums.
SELECT AVG(stock) AS all_albums,
AVG(NULLIF(stock, 0)) AS in_stock_only
FROM albums;Worked solution
SELECT id, CASE WHEN status='refunded' THEN 'review' WHEN status='cancelled' THEN 'closed' WHEN total_cents>=3000 THEN 'priority' ELSE 'standard' END AS route FROM shop_orders ORDER BY id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
