Price bands for products
Medium · Online shop database · CASE
Put every product in a price band:
budgetwhen the price is below 20standardfrom 20 up to, but not including, 50premiumfor 50 and above
Return name, price and the band, in that order. Any row order.
Expected result
| name | price | band |
|---|---|---|
| Wireless Mouse | 24.99 | standard |
| Mechanical Keyboard | 89.5 | premium |
| USB-C Hub | 39 | standard |
| Noise Cancelling Headphones | 199 | premium |
| Intro to Statistics | 48 | standard |
| The Quiet Algorithm | 18.5 | budget |
| World Atlas | 29.99 | standard |
| Gel Pen Set | 6.49 | budget |
| Spiral Notebook | 3.99 | budget |
| Desk Organizer | 20 | standard |
| Fountain Pen | 50 | premium |
| Ceramic Mug | 12 | budget |
| Table Lamp | 54.99 | premium |
| Cotton Throw | 39 | standard |
| Yoga Mat | 27.5 | standard |
| Steel Water Bottle | 15.99 | budget |
| Running Shoes | 119 | premium |
| Badminton Racket | 34 | standard |
The online shop tables
customers: 18 rows, first 5 shown
| id | name | city | country | signup_date | phone |
|---|---|---|---|---|---|
| 1 | Ravi Kumar | Bengaluru | India | 2025-01-12 | +91 98450 12345 |
| 2 | Emily Johnson | Austin | USA | 2025-01-25 | +1 512 555 0142 |
| 3 | Hiroshi Sato | Osaka | Japan | 2025-02-08 | NULL |
| 4 | Ana Souza | Sao Paulo | Brazil | 2025-02-19 | +55 11 91234 5678 |
| 5 | Priya Sharma | Mumbai | India | 2025-03-03 | +91 99200 54321 |
order_items: 40 rows, first 5 shown
| id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 22.99 |
| 2 | 1 | 9 | 3 | 3.99 |
| 3 | 2 | 4 | 1 | 189 |
| 4 | 2 | 16 | 2 | 15.99 |
| 5 | 3 | 2 | 1 | 89.5 |
orders: 28 rows, first 5 shown
| id | customer_id | order_date | status |
|---|---|---|---|
| 1 | 1 | 2025-02-03 | delivered |
| 2 | 2 | 2025-02-14 | delivered |
| 3 | 3 | 2025-03-01 | delivered |
| 4 | 1 | 2025-03-22 | cancelled |
| 5 | 4 | 2025-04-05 | delivered |
products: 18 rows, first 5 shown
| id | name | category | price | stock |
|---|---|---|---|---|
| 1 | Wireless Mouse | Electronics | 24.99 | 120 |
| 2 | Mechanical Keyboard | Electronics | 89.5 | 45 |
| 3 | USB-C Hub | Electronics | 39 | 80 |
| 4 | Noise Cancelling Headphones | Electronics | 199 | 25 |
| 5 | Intro to Statistics | Books | 48 | 60 |
-- Write your SQL here