Conditional Categorization Using CASE WHEN in SQL

Question

Given an `orders` table, categorize each order into a `priority_level` based on delivery distance in kilometers: <= 3km as 'Express Urban', between 3.01 and 8km as 'Standard Metro', and > 8km as 'Extended Distance'. Return `order_id`, `distance_km`, and `priority_level`.

Accepted Answer

Optimal Solution:
SELECT 
  order_id,
  distance_km,
  CASE 
    WHEN distance_km <= 3.0 THEN 'Express Urban'
    WHEN distance_km <= 8.0 THEN 'Standard Metro'
    ELSE 'Extended Distance'
  END AS priority_level
FROM orders
ORDER BY order_id ASC;

Technical Explanation:
The CASE expression evaluates conditions in order from top to bottom. Because conditions short-circuit upon the first match, the second condition `distance_km <= 8.0` implicitly checks `> 3.0`.

order_id	restaurant_id	distance_km	order_status
501	12	2.4	delivered
502	14	5.8	delivered
503	19	11.2	delivered

order_id	distance_km	priority_level
501	2.4	Express Urban
502	5.8	Standard Metro
503	11.2	Extended Distance