Product Price at Date
Amazon
Problem Statement
Amazon inventory systems track every price change for every product. Management needs a report showing the price of every product exactly on 2019-08-16.
The price of a product at a given date is the price set by the most recent change on or before that date. If a product has no price changes recorded on or before 2019-08-16, its price is assumed to be 10.
Write a query to find the prices of all products on 2019-08-16.
Rules:
- Include every product that exists in the system.
- If no price change exists on or before 2019-08-16, the price defaults to 10.
- Output columns: product_id, price.
- Results must be sorted by product_id in ascending order.
Tests your understanding of
Window Functions, Subqueries, Grouping and Temporal Data
Input Tables
Products
| product_id(INTEGER) | product_name(VARCHAR) |
|---|---|
| 1 | Echo Dot |
| 2 | Kindle Paperwhite |
| 3 | Fire Stick |
| 4 | Ring Doorbell |
| 5 | Cloud Cam |
| 6 | Smart Plug |
Price_Logs
| product_id(INTEGER) | new_price(DECIMAL) | change_date(DATE) |
|---|---|---|
| 1 | 20 | 2019-08-14 |
| 2 | 50 | 2019-08-14 |
| 1 | 30 | 2019-08-15 |
| 1 | 35 | 2019-08-16 |
| 2 | 65 | 2019-08-17 |
| 3 | 20 | 2019-08-18 |
Warehouse_Inventory
| product_id(INTEGER) | stock_count(INTEGER) |
|---|---|
| 1 | 100 |
| 2 | 50 |
| 3 | 200 |
| 4 | 0 |
| 5 | 10 |
| 6 | 5 |
Expected Output
| product_id(INTEGER) | price(DECIMAL) |
|---|---|
| 1 | 35 |
| 2 | 50 |
| 3 | 10 |
| 4 | 10 |
| 5 | 10 |
| 6 | 10 |
Tags
MediumWindow FunctionsSubqueriesGroupingTemporal Data
15-20 min
65%