Label each basket as Small, Medium or Large

Question

A merchandising team buckets shopping baskets by spend so it can size up its packaging inventory.

For every row in `basket_summary`, return `basket_id`, `basket_value` and a derived column named `size_label`:

- `Small` when `basket_value` is below 1000
- `Medium` when it is at least 1000 but below

Accepted Answer

Step 1: A `CASE` expression evaluates its `WHEN` branches top to bottom and stops at the first match, so the bucket order matters. Step 2: Put the lowest bucket first: `WHEN basket_value < 1000 THEN 'Small'`. Step 3: The next branch only sees rows that already failed the first test, so `WHEN basket_value < 5000 THEN 'Medium'` implicitly means 1000–4999.99. Step 4: Everything remaining is 5000 or more, so an `ELSE 'Large'` closes the expression. Step 5: Alias the expression as `size_label` and select it alongside `basket_id` and `basket_value`.

Label each basket as Small, Medium or Large

More CASE Statements questions

Asked at Myntra