Stock status
easyStock status
Amazon SQL Interview Question
Amazon's fulfillment team wants a simple stock label next to each product on its inventory dashboard.
Show the product_name, stock_quantity and label as stock_status for every product. The label is "Out of stock" when there are no units, "Low stock" for 1 to 19 units, and "In stock" for 20 or more units.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Scientist
productsTable15 rows
| Column Name | Type |
|---|---|
| product_id | BIGINT |
| product_name | VARCHAR |
| category | VARCHAR |
| brand | VARCHAR |
| price | DOUBLE |
| discount_pct | BIGINT |
| stock_quantity | BIGINT |
| launch_date | DATE |
| is_active | BOOLEAN |
productsExample Input
| product_id | product_name | category | brand | price | discount_pct | stock_quantity | launch_date | is_active |
|---|---|---|---|---|---|---|---|---|
| 1 | Wireless Mouse | Electronics | Logitech | 24.99 | 10 | 150 | 2023-02-14 | true |
| 2 | Mechanical Keyboard Pro | Electronics | Keychron | 119 | NULL | 40 | 2023-05-01 | true |
| 3 | Noise Canceling Headphones | Electronics | Sony | 299.99 | 15 | 0 | 2022-11-20 | true |
| 4 | Standing Desk | Furniture | Flexispot | 349 | NULL | 12 | 2023-08-09 | true |
| 5 | Ergonomic Chair Pro | Furniture | Steelcase | 1299 | 5 | 5 | 2021-06-30 | true |
| 6 | Desk Lamp | Furniture | NULL | 39.5 | NULL | 80 | 2024-01-15 | true |
| 7 | USB-C Hub | Electronics | Anker | 45 | 20 | 200 | 2023-03-22 | true |
| 8 | Notebook Set | Stationery | Moleskine | 18 | NULL | 0 | 2022-09-01 | false |
Example Output
| product_name | stock_quantity | stock_status |
|---|---|---|
| Desk Lamp | 80 | In stock |
| Ergonomic Chair Pro | 5 | Low stock |
| Mechanical Keyboard Pro | 40 | In stock |
| Noise Canceling Headphones | 0 | Out of stock |
| Notebook Set | 0 | Out of stock |
| Standing Desk | 12 | Low stock |
| USB-C Hub | 200 | In stock |
| Wireless Mouse | 150 | In stock |
Explanation
The Noise Canceling Headphones and Notebook Set have no units, so they are Out of stock. The Ergonomic Chair Pro has 5 units and the Standing Desk has 12, so both are Low stock. Everything else has 20 or more units.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Amazon
Difficulty
easy
Topic
conditional logic
Your query
Starting the SQL engine...