Inventory Stock-Out Days
Amazon
Problem Statement
Efficient supply chain management requires identifying prolonged stock-outs that impact customer experience.
Write a query to find all dates where a product had a stock level of zero and belonged to a period of at least four consecutive days of zero stock. For example, if a product had zero stock from March 1st to March 5th, all those five dates should be returned. If it was zero only for two days, none should be returned.
Rules:
- Output columns: product_id, stock_out_date.
- A "Stock-Out period" is defined as > 3 consecutive days (meaning 4 or more days).
- Result must be sorted by product_id ascending, and then by stock_out_date ascending.
- Assume the inventory_logs table has one entry per product per day.
Tests your understanding of
Window Functions, Gaps and Islands and Date Arithmetic
Input Tables
inventory_logs
| product_id(INTEGER) | log_date(DATE) | stock_quantity(INTEGER) |
|---|---|---|
| 101 | 2024-01-01 | 10 |
| 101 | 2024-01-02 | 0 |
| 101 | 2024-01-03 | 0 |
| 101 | 2024-01-04 | 0 |
| 101 | 2024-01-05 | 0 |
| 101 | 2024-01-06 | 5 |
| 102 | 2024-01-01 | 0 |
| 102 | 2024-01-02 | 0 |
| 102 | 2024-01-03 | 0 |
Expected Output
| product_id(INTEGER) | stock_out_date(DATE) |
|---|---|
| 101 | 2024-01-02 |
| 101 | 2024-01-03 |
| 101 | 2024-01-04 |
| 101 | 2024-01-05 |
Tags
HardWindow FunctionsGaps and IslandsDate Arithmetic
40-50 min
31%