SQL interview question: Inventory Reorder Point
Calculate Reorder Point for Inventory
Supply chain needs to determine when to reorder products based on average daily sales and lead time.
Calculate reorder points: (avg_daily_sales × lead_time_days) + safety_stock
Return product_name, reorder_point, current_stock, and should_reorder ('Yes' if current_stock < reorder_point, else 'No').
Skills: complex business logic with CASE statements
Tables
product_inventory 4 rows
- product_id integer PK
- product_name text
- current_stock integer
- avg_daily_sales real
- lead_time_days integer
- safety_stock integer
| product_id | product_name | current_stock | avg_daily_sales | lead_time_days | safety_stock |
|---|---|---|---|---|---|
| 1 | Widget A | 500 | 25.0 | 7 | 50 |
| 2 | Widget B | 300 | 40.0 | 10 | 100 |
| 3 | Widget C | 200 | 15.0 | 5 | 30 |