Inventory Aging
ASKED IN INTERVIEWAmazon
Problem Statement
Amazon’s Fulfillment Centers need to track "Aging Inventory" to avoid long-term storage fees and manage cash flow. Inventory that sits too long in the warehouse is a liability.
Write a query to categorize all current inventory into three age buckets based on how long it has been in the warehouse:
- 0-30 days
- 31-60 days
- 60+ days
The "Current Date" for this problem is 2026-02-14.
Rules:
- Age is calculated as: Current_Date - arrival_date.
- Total Value for a batch is: quantity * unit_cost.
- You must report the total value of inventory for each warehouse in each bucket.
- Output columns: warehouse_id, age_bucket, total_value.
- Sort the result by warehouse_id in ascending order, then by the bucket (0-30 first, then 31-60, then 60+).
Table Schema:
- Inventory_Batches: batch_id, product_id, warehouse_id, quantity, unit_cost, arrival_date.
- Warehouses: warehouse_id, warehouse_name, location.
- Products: product_id, product_name, category.
Tests your understanding of
Case Statements, Date Arithmetic, Aggregation and Subqueries
Input Tables
Inventory_Batches
| batch_id(INTEGER) | warehouse_id(INTEGER) | quantity(INTEGER) | unit_cost(DECIMAL) | arrival_date(DATE) |
|---|---|---|---|---|
| 1 | 10 | 100 | 5 | 2026-02-10 |
| 2 | 10 | 50 | 10 | 2026-01-15 |
| 3 | 10 | 20 | 25 | 2025-12-01 |
| 4 | 20 | 200 | 2 | 2026-02-01 |
| 5 | 20 | 10 | 100 | 2025-11-15 |
| 6 | 10 | 30 | 15 | 2026-01-20 |
Warehouses
| warehouse_id(INTEGER) | warehouse_name(VARCHAR) |
|---|---|
| 10 | SEA-1 |
| 20 | NYC-5 |
Products
| product_id(INTEGER) | product_name(VARCHAR) |
|---|---|
| 101 | Kindle |
Expected Output
| warehouse_id(INTEGER) | age_bucket(VARCHAR) | total_value(DECIMAL) |
|---|---|---|
| 10 | 0-30 | 1450 |
| 10 | 60+ | 500 |
| 20 | 0-30 | 400 |
| 20 | 60+ | 1000 |
Tags
MediumASKED IN INTERVIEWCase StatementsDate ArithmeticAggregationSubqueries
20-25 min
38%