Skip to main content
Talentd

Product Price at a Given Date - Solution & Explanation

MediumDatabase7 min readAsked at: Amazon, Microsoft, Meta +2
Practice this problem

Problem Statement

Table: Products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+
(product_id, change_date) is the primary key (combination of columns with unique values) of this table.
Each row of this table indicates that the price of some product was changed to a new price at some date.

 

Write a solution to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Products table:
+------------+-----------+-------------+
| 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  |
+------------+-----------+-------------+
Output: 
+------------+-------+
| product_id | price |
+------------+-------+
| 2          | 50    |
| 1          | 35    |
| 3          | 10    |
+------------+-------+

Approach Overview

Problem Overview: You are given a table of product price changes where each record stores product_id, new_price, and the change_date. The task is to return the price of every product on a specific date. If a product has no recorded price change before that date, its price defaults to 10. The challenge is determining the most recent price update that occurred on or before the given date for each product.

Approach 1: Sort and Group by Product ID and Change Date (O(n log n) time, O(n) space)

This approach processes the data similarly to how you would handle event timelines. First, sort all records by product_id and change_date. Then iterate through the sorted list while grouping entries by product. For each product, keep updating the current price whenever the change_date is less than or equal to the target date. The last valid update becomes the product's price. If no valid update exists, return the default price of 10. Sorting ensures chronological processing, while grouping makes it easy to track the latest valid change for each product.

This technique is straightforward in languages like Python or JavaScript using sorting plus dictionary/group structures. It works well when records must be processed in memory and mirrors patterns used in sorting and timeline-style data problems.

Approach 2: Use SQL-like Logic to Handle Dates (O(n) time, O(n) space)

A more database-oriented solution directly models the problem as a query. For each product, you want the row with the maximum change_date that is still less than or equal to the target date. This can be implemented using filtering plus aggregation logic similar to SQL's MAX(change_date) with grouping. First filter records where change_date <= target_date, then determine the latest change per product. Join or map that result back to retrieve the corresponding price. If a product has no qualifying record, assign the default value.

This mirrors patterns used in database and SQL queries where you compute the latest event before a timestamp. In languages like C++ or C#, you can replicate this with hash maps that track the best date seen per product while iterating through the dataset.

Recommended for interviews: The SQL-style aggregation approach is typically preferred. It directly models the requirement: "latest price change before a given date." Interviewers expect candidates to recognize the max-date-per-group pattern and implement it efficiently. The sorting and grouping approach still demonstrates solid reasoning and works well when the dataset is processed sequentially.

Approach 1: Approach 1: Sort and Group by Product ID and Change Date

In this approach, we can utilize sorting and grouping to find the last change before or on the specified date. We'll sort the input by product_id and change_date thus facilitating checking the last change on or before 2019-08-16. If no change is present by that date, we assign a default price of 10 for those products.

This solution uses a default dictionary initialized with base price 10. We iterate through the sorted list of products by product_id and date. For each product, if the change date is on or before the target date, we update the product's price in the dictionary. Finally, we prepare a list of results showing the product_id and its corresponding price on the given date.

Code

PythonJavaScript

Python

JavaScript

Complexity

Time Complexity: O(N log N), due to sorting the records.
Space Complexity: O(N), for storing the latest prices of the products.

Try this approach in the editor →

Approach 2: Approach 2: Use SQL-like Logic to Handle Dates

This approach simulates SQL operations in code logic to find the latest price for a given product_id on or before 2019-08-16. This can be done by first grouping by product_id and filtering with a date condition.

This C++ code uses STL map to mimic SQL grouping and filtering. It iterates over each record, checking change dates and storing the maximum price for each product that is valid for the date. The final step is to collect results by comparing stored prices against the base price.

Code

C++C#

C++

C#

Complexity

Time Complexity: O(N log N), primarily due to map operations.
Space Complexity: O(N), to store product_id and price mappings.

Try this approach in the editor →

Approach 3: Subquery + Join

We can use a subquery to find the price of the last price change for each product before the given date, and record it in the P table. Then, we can find all product_ids in the T table. Finally, we can left join the T table with the P table on product_id to get the final result.

Code

MySQL

MySQL

Try this approach in the editor →

Approach 4: Default Approach

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
Approach 1: Sort and Group by Product ID and Change Date

Time Complexity: O(N log N), due to sorting the records.
Space Complexity: O(N), for storing the latest prices of the products.

Approach 2: Use SQL-like Logic to Handle Dates

Time Complexity: O(N log N), primarily due to map operations.
Space Complexity: O(N), to store product_id and price mappings.

Subquery + Join—
Default Approach—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Sort and Group by Product ID and Change DateO(n log n)O(n)When processing records sequentially in application code or when sorting simplifies timeline tracking.
SQL-like Date Filtering with Latest ChangeO(n)O(n)Best for database-style problems where you need the latest record before a timestamp for each group.

Video Solution

Product Price at a Given Date | Leetcode 1164 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 15,210 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Product Price at a Given Date easy or hard?
Product Price at a Given Date is classified as a Medium difficulty problem. The main challenge is recognizing the pattern of selecting the most recent record before a given timestamp for each group, which is common in database and SQL interview questions.
Product Price at a Given Date Python/Java solution
Python implementations typically sort records or use dictionaries to track the latest valid change date per product. Java solutions often use HashMap structures or database-style grouping logic. Both approaches achieve O(n) or O(n log n) complexity depending on whether sorting is required.
How to solve Product Price at a Given Date in O(n)?
Iterate through the records while keeping a hash map that stores the latest valid change_date for each product where change_date <= target_date. Update the stored price whenever a later valid date is found. After processing all records, return the tracked price for each product or the default value 10 if no update exists.
What is the best approach for Product Price at a Given Date?
The most effective approach uses SQL-style logic: filter rows where change_date is less than or equal to the target date, then select the maximum change_date per product. This directly identifies the most recent price update before the given date. The method runs in O(n) time with O(n) space using hash maps or database grouping.
Is Product Price at a Given Date asked at Google/Amazon/Meta?
Database and SQL-style query problems like this frequently appear in interviews at companies such as Amazon, Google, and Meta. They test understanding of grouping, filtering by date, and retrieving the most recent record per entity.
What data structure is used in Product Price at a Given Date?
Common solutions use hash maps or dictionaries to store the latest price change per product while iterating through records. Sorting structures or SQL GROUP BY logic may also be used to organize updates by product and date.
What is the time complexity of Product Price at a Given Date?
The optimal approach runs in O(n) time by scanning all price change records once and tracking the latest valid change for each product. A sorting-based approach requires O(n log n) time due to ordering by product_id and change_date.

Ready to solve this problem?

Practice Product Price at a Given Date with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary