Product Price at a Given Date - Problem

Given a table Products that tracks price changes for products over time, find the price of all products on a specific date (2019-08-16).

The table has the following structure:

  • product_id: The unique identifier for each product
  • new_price: The new price after the change
  • change_date: The date when the price was changed

Key Rules:

  • All products initially have a price of 10
  • If a product's price was never changed before or on 2019-08-16, return price 10
  • If a product had multiple price changes, use the most recent price on or before 2019-08-16

Table Schema

Products
Column Name Type Description
product_id PK int Unique identifier for each product
new_price int The new price after the change
change_date PK date The date when the price was changed
Primary Key: (product_id, change_date)
Note: Each row represents a price change for a product. All products initially have price 10.

Input & Output

Example 1 — Multiple Price Changes
Input Table:
product_id new_price change_date
1 20 2019-08-14
2 50 2019-08-15
1 30 2019-08-17
3 35 2019-08-18
Output:
product_id price
1 20
2 50
3 10
💡 Note:

For product 1: Price changed to 20 on 2019-08-14 (before target date), then to 30 on 2019-08-17 (after target date). So we use 20.

For product 2: Price changed to 50 on 2019-08-15 (before target date). So we use 50.

For product 3: Price changed to 35 on 2019-08-18 (after target date). No changes before target date, so we use default price 10.
Example 2 — Exact Date Match
Input Table:
product_id new_price change_date
1 25 2019-08-16
2 40 2019-08-15
2 45 2019-08-16
Output:
product_id price
1 25
2 45
💡 Note:

For product 1: Price changed exactly on target date 2019-08-16 to 25.

For product 2: Had two price changes - 40 on 2019-08-15 and 45 on 2019-08-16. We use the most recent change on the target date, which is 45.
Example 3 — No Price Changes
Input Table:
product_id new_price change_date
5 100 2019-08-20
Output:
product_id price
5 10
💡 Note:

Product 5 had a price change on 2019-08-20, which is after our target date 2019-08-16. Since there are no price changes on or before the target date, we return the default price of 10.

Constraints

  • 1 ≤ product_id ≤ 1000
  • 0 ≤ new_price ≤ 1000000
  • change_date is a valid date between 2000-01-01 and 2050-12-31

Visualization

Tap to expand
Product Price at a Given Date INPUT product_id new_price change_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 Target Date: 2019-08-16 Default price = 10 (if no change before date) Timeline View 08-14 08-15 08-16 08-17+ ALGORITHM STEPS 1 Filter Valid Changes WHERE change_date <= target Product 1: 08-14, 08-15, 08-16 Product 2: 08-14 only Product 3: (none before 08-16) 2 Get Latest Price MAX(change_date) per product Product 1: 08-16 --> price=35 Product 2: 08-14 --> price=50 3 Find Missing Products No price change before date Product 3: assign default = 10 4 UNION Results Combine both queries SELECT product_id, new_price FROM (subquery with MAX date) UNION SELECT product_id, 10 (default) FINAL RESULT product_id price 1 35 2 50 3 10 Product 1: Changed on 08-16 Product 2: Last change 08-14 (before target date) Product 3: No change before 08-16 Uses default price = 10 Query Complete 3 products found Key Insight: Use a subquery with MAX(change_date) to find the most recent price change on or before the target date. Then UNION with products that have no changes before target date (assign default price 10). The NOT IN clause identifies products without any valid price changes before the specified date. TutorialsPoint - Product Price at a Given Date | Optimal Solution (UNION + Subquery)

Related Problems

Asked in
Amazon 15 Google 12 Microsoft 8
23.4K Views
Medium Frequency
~18 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen