Product Price at a Given Date
Wayfair
Problem Statement
Wayfair maintains a price history log for all products. Every product starts with an initial price of 10. When a price is updated, a new record is added to the products table.
Write an SQL query to find the prices of all products on 2019-08-16.
Rules:
- If a product has no price changes on or before 2019-08-16, its price is 10.
- If a product has multiple changes before the date, use the one with the latest change_date.
- The result table should have columns product_id and price.
- Results must be sorted by product_id in descending order.
Tests your understanding of
Basic SQL, Window Functions, Subquery, COALESCE and RANK
Input Tables
products
| product_id(INTEGER) | new_price(INTEGER) | 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 |
| 4 | 40 | 2019-08-10 |
| 5 | 100 | 2019-08-01 |
| 5 | 110 | 2019-08-20 |
| 6 | 25 | 2019-08-16 |
| 7 | 99 | 2019-09-01 |
| 8 | 5 | 2019-08-15 |
Expected Output
| product_id(INTEGER) | price(INTEGER) |
|---|---|
| 8 | 5 |
| 7 | 10 |
| 6 | 25 |
| 5 | 100 |
| 4 | 40 |
| 3 | 10 |
| 2 | 50 |
| 1 | 35 |
Tags
MediumBasic SQLWindow FunctionsSubqueryCOALESCERANK
20-25 min
48%