Product Price at a Given Date - Practice Coding Problems

Product Price at a Given Date - Problem

Database Medium

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

Understanding the Visualization

Filter

Keep only changes ≤ target date

Rank

Find most recent change per product

Default

Use price 10 for products with no changes

Key Takeaway

🎯 Key Insight: Use window functions to find the most recent price change before or on the target date, with COALESCE for default pricing

Asked in

A Amazon 15 G Google 12 M Microsoft 8

Use window functions with ROW_NUMBER() to find the most recent price change for each product on or before the target date. Handle products with no valid changes using COALESCE with default price 10.

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.

Common Approaches

Approach	Time	Space	Notes
✓ Common Table Expression (CTE)	O(n log n)	O(n)	Use CTE to organize the logic: find latest prices and handle products with no changes
Window Function with ROW_NUMBER	O(n log n)	O(n)	Use ROW_NUMBER() to find the most recent price change for each product on or before the target date

Common Table Expression (CTE) — Algorithm Steps

Step 1: Create CTE to find latest valid price for each product
Step 2: Create CTE to get all distinct products
Step 3: LEFT JOIN to combine, using COALESCE for default price 10

Visualization

Tap to expand

Step-by-Step Walkthrough

CTE 1

Find latest prices per product

CTE 2

Get all product IDs

Join

Combine with COALESCE for defaults

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting required for finding latest date

⚡ Linearithmic

Space Complexity

O(n)

CTE temporary storage

⚡ Linearithmic Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Common Table Expression (CTE) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler