Implement a CASE WHEN condition - medium difficulty

CASE WHEN implements conditional logic. Medium example—categorize sales: SELECT customer_id, amount, CASE WHEN amount >= 1000 THEN 'High' WHEN amount >= 100 THEN 'Medium' WHEN amount > 0 THEN 'Low' ELSE 'Zero' END AS tier FROM orders. Nested CASE: CASE WHEN region = 'US' THEN CASE WHEN state IN ('CA','NY') THEN 'Tier1' ELSE 'Tier2' END ELSE 'International' END. Use for bucketing, NULL handling, and derived columns. Best practice: list conditions from most to least specific; include ELSE for exhaustiveness. In aggregation: SELECT CASE WHEN age < 18 THEN 'Minor' ELSE 'Adult' END AS group, COUNT(*) FROM users GROUP BY 1. Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.