Label orders by value band

Return order_id, total_amount, and order_band for each order. order_band is 'high_value' for total_amount >= 500, 'standard' for >= 100, otherwise 'small'. Order by total_amount descending, then order_id ascending, and return the top 8.

Write a CASE with the >= 500 branch first, alias it order_band, then ORDER BY total_amount DESC, order_id ASC LIMIT 8.

Sample input

ordersA slice of the 12 orders by amount (the sandbox has all 12).

order_id	total_amount
1010	640
1005	540
1004	220
1008	150
1002	120
1011	110
1007	95
1001	80
1003	40

Expected output

Expected outputTop 8 by total_amount desc, order_id asc. The >= 500 branch is checked first, so 640 and 540 are high_value.

order_id	total_amount	order_band
1010	640	high_value
1005	540	high_value
1004	220	standard
1008	150	standard
1002	120	standard
1011	110	standard
1007	95	small
1001	80	small

Constraints

Branch order matters: check the highest threshold first so a 640 order is not caught by the 100 branch. Alias the derived column order_band. Use total_amount DESC, order_id ASC, limited to 8 rows.

Expected skills

CASE branch ordering, aliasing derived columns, and deterministic ORDER BY with a tie-breaker.

SQL

AI evaluation

Run the SQL query to inspect preview rows.