How to Find Product Prices on a Specific Date Using SQL | Leetcode Interview Question Explained

Many SQL interview questions test your ability to work with data that changes over time. One common task is finding the price of products on a certain date, especially when product prices have changed multiple times. This blog post will walk you through solving such a problem step by step, using a real SQL interview question from Leetcode.

The goal is to find the prices of all products on August 16, 2019, given a table that stores product price changes with their effective dates. You'll learn how to handle initial prices, price changes before and after that date, and how to combine these results in a single query.

Watch the full walkthrough here:

Understanding the Problem Statement

Imagine you have a table named products with three important columns:

• product_id: The unique ID of the product.
• price: The price set for this product at a certain time.
• change_date: The date when this price became effective.

Your task is to find the price of every product on a specific date, in this case, August 16, 2019.

One key assumption is that all products start with a default price of 10, before any price changes were made.

Here's how the pricing works:

• For product ID 1, the price on August 16, 2019, is 35 because it had a price change on or before this date.
• For product ID 3, the price only changed on August 18, 2019, which is after the target date. So, on August 16, 2019, its price is still the initial 10.

This problem tests how well you can track price history and apply initial price assumptions.

Approach to Solving the Query: Breaking Down the Dataset

The simplest way to tackle this query is to break all products into two groups based on their price changes relative to August 16, 2019.

1. Products with price changes on or before August 16, 2019.
   For these products, you want the most recent price change up to that date.
2. Products with price changes only after August 16, 2019.
   These products have no recorded price changes before the date, so their price on August 16 is the initial price of 10.

By separating the products like this, you can write simpler queries for each group and then combine the results.

How to separate the two groups in SQL

• Use GROUP BY product_id to look at each product.
• Use the HAVING clause to filter by dates.
  • For products with changes only after August 16, find those where the minimum change date is greater than August 16.

Here’s an example snippet to find products with price changes only after August 16:

SELECT product_id
FROM products
GROUP BY product_id
HAVING MIN(change_date) > '2019-08-16'

This identifies products that had no price changes before or on August 16.

Handling Products with Price Changes Only After the Target Date

Products without any price changes on or before August 16 still have a price—it's the starting price of 10.

To list these products with their prices on August 16:

• Find products where all price changes are after August 16 (using the query above).
• Assign their price as 10.

Here’s how that query looks:

SELECT product_id, 10 AS price
FROM products
GROUP BY product_id
HAVING MIN(change_date) > '2019-08-16'

For example, product ID 3 falls into this group, so its price on August 16 is set to 10.

Handling Products with Price Changes On or Before the Target Date

For products that had price changes on or before August 16, you need to find their most recent price change up to that date.

The steps are:

1. Filter rows where change_date <= '2019-08-16'.
2. For each product, find the maximum change_date in that filtered set — this represents the latest price update before or on August 16.
3. Use a Common Table Expression (CTE) to hold these maximum change dates for each product. This makes the query easier to read and reuse.
4. Join the CTE back to the original products table to get the price that corresponds to the maximum change date for each product.

Here’s the SQL query to do that:

WITH last_price_changes AS (
    SELECT product_id, MAX(change_date) AS max_change_date
    FROM products
    WHERE change_date <= '2019-08-16'
    GROUP BY product_id
)
SELECT p.product_id, p.price
FROM products p
JOIN last_price_changes lpc
  ON p.product_id = lpc.product_id
 AND p.change_date = lpc.max_change_date

This query returns the correct price for each product that had one or more price changes on or before August 16.

Combining Results for the Final Output

At this point, you have two sets of results:

• Products priced based on the latest change on or before August 16.
• Products priced at the starting price of 10 if their changes start after August 16.

To get the complete list of product prices on August 16, combine both results using UNION ALL.

Here’s the final query putting everything together:

WITH last_price_changes AS (
    SELECT product_id, MAX(change_date) AS max_change_date
    FROM products
    WHERE change_date <= '2019-08-16'
    GROUP BY product_id
),
prices_before_date AS (
    SELECT p.product_id, p.price
    FROM products p
    JOIN last_price_changes lpc
      ON p.product_id = lpc.product_id
     AND p.change_date = lpc.max_change_date
),
prices_after_date AS (
    SELECT product_id, 10 AS price
    FROM products
    GROUP BY product_id
    HAVING MIN(change_date) > '2019-08-16'
)
SELECT * FROM prices_before_date
UNION ALL
SELECT * FROM prices_after_date
ORDER BY product_id;

This query ensures:

• Every product appears exactly once.
• Prices reflect the correct value on August 16, 2019.
• The default price of 10 is applied when no earlier changes exist.

Summary of SQL Concepts and Techniques Applied

This solution demonstrates several key SQL concepts useful for working with date-based records:

• Grouping data by product to process each item independently.
• Using the HAVING clause to filter groups based on aggregate conditions (MIN(change_date)).
• Aggregation with MAX() to find the most recent applicable price change.
• Using a Common Table Expression (CTE) for better organization and readability.
• Joining tables on multiple conditions (product_id and change_date) to get matching records.
• Combining separate query results with UNION ALL to cover all products.

These techniques are broadly applicable to many kinds of temporal data queries, not just price histories.

Additional Resources and Practice Suggestions

For a hands-on walkthrough and more detailed explanations, watch the video by Lan Noar titled SQL Interview Question | How To Find Product Price On a Specific Date? Leetcode | Advanced SQL.

Practicing similar problems on platforms like Leetcode builds confidence for SQL interviews and helps you master querying historical data.

If you want personalized coaching, Lan Noar offers 1-on-1 SQL mock interviews that can be booked through their scheduling page at Calendly.

For more SQL lessons and tips, explore the Learn at Knowstar YouTube channel and their blog.

---

This method for working with time-based price data will help you answer many real-world questions and perform well in SQL data interviews showing your understanding of date filtering, aggregation, and query organization. Keep practicing!