The merchandising team wants to classify products into price categories based on their listed prices.
Using the products table, assign a price band to every product:
01budget: Products priced below 50.
02mid: Products priced from 50 up to, but not including, 200.
03premium: Products priced at 200 or more.
Ordering: Sort by product_id in ascending order.
Returnproduct_idcategorylist_priceprice_band
About this problem
You practise turning numeric ranges into labels with CASE WHEN. The merchandising team wants every product in products placed in a band: budget below 50, mid from 50 up to but not including 200, and premium at 200 or more. You return product_id, category, list_price and the new price_band, sorted by product_id. The skill is writing branches whose order and boundaries match the stated ranges exactly.
CASE returns the first branch that matches, so the order of your WHEN clauses is part of the logic. If you start with a broad test such as list_price >= 50 for mid, every product at 200 or above is caught there and premium can never be returned. The output still looks sensible, because every row gets a label. Boundaries are the other risk. The sample has products priced at exactly 50 and exactly 200, and those are the rows where > against >=, or < against <=, decides the band. A product at 50 is mid, not budget; one at 200 is premium, not mid. Check the rows that sit exactly on a boundary first.
Read the lesson: Conditional Logic & CASE (P05) →