Product Price at a Given Date | LeetCode 1164

1. Problem Overview & Objective

In this problem, we are given a database table tracking product price changes over time, and our task is to find the exact price of every product on a specific target date: 16th August 2019.

The core challenge revolves around temporal data management in SQL: determining whether a product's price had changed on or before the target date, picking the most recent valid price if multiple changes occurred, or defaulting to an initial baseline price if no changes happened prior to the target date.

2. Table Schema & Constraints

The problem provides a single table named products with three columns:

Key Constraints & Rules:

• Primary Key: The combination of (product_id, change_date) is unique. This means a single product can have multiple price changes on different dates, but it cannot have two different price changes on the exact same date.
• Baseline Price Rule: The problem statement explicitly states: "Assume the price of all products before any change is 10."
• Order Independence: The result table can be returned in any order.

3. Real-Life Mental Model & Analogy

Real-Life Analogy: Price Tag Updates in a Retail Store

Imagine you manage a retail store catalog. Each product has a default manufacturer baseline price of $10. Over months and weeks, price update memos arrive with specific effective dates:

• Product #1 starts at $10. On August 10, it updates to $20. On August 14, it updates to $30. On August 16, it updates to $35.
• If an auditor walks in on August 16 and asks, "What was the exact price of Product #1 on this day?", you look back through all price tags effective on or before August 16. You discard future price tags (e.g., changes happening on August 17 or 18) and find the most recent tag applied up to that date, which is $35.
• For Product #3, the only price update memo arrives on August 18 ($50). Since the auditor is checking prices on August 16, that future update hasn't happened yet. Therefore, Product #3 still carries its default baseline price of $10.

4. Step-by-Step Manual Walkthrough with Example

Let's trace the sample dataset provided in the problem to understand how individual product prices are evaluated on 16th August 2019.

Sample Input Table (products)

Manual Evaluation per Product ID:

1. Product ID 1:

   • Baseline price: 10
   • Changes recorded:
     • Aug 10 $\rightarrow$ 20
     • Aug 15 $\rightarrow$ 30
     • Aug 16 $\rightarrow$ 35
   • Target Date: 16th August 2019
   • Filtering changes on or before Aug 16: All three changes qualify. We pick the maximum (most recent) change date, which is 2019-08-16. The corresponding new_price is 35.

2. Product ID 2:

   • Baseline price: 10
   • Changes recorded:
     • Aug 14 $\rightarrow$ 50
     • Aug 17 $\rightarrow$ 65
   • Target Date: 16th August 2019
   • Filtering changes on or before Aug 16: Only the change on Aug 14 (50) qualifies (the Aug 17 change is in the future relative to our target date). The most recent valid price is 50.

3. Product ID 3:

   • Baseline price: 10
   • Changes recorded:
     • Aug 18 $\rightarrow$ 20
   • Target Date: 16th August 2019
   • Filtering changes on or before Aug 16: None. No price changes occurred on or before August 16. Therefore, Product #3 falls back to the default baseline price of 10.

Expected Output Table:

5. Naive Approach vs. Production-Ready Strategy

When approaching temporal state queries in SQL, developers often run into pitfalls:

• Naive Approach (Filtering inside a single pass): Simply writing WHERE change_date <= '2019-08-16' will completely omit products (like Product #3) that never had a price change on or before that date. This results in missing rows in the output.
• Production-Ready Strategy (Union Pattern):
  • Part A: Find all products that did experience at least one price change on or before the target date, and extract their most recent price using aggregation (MAX(change_date)).
  • Part B: Find all products that never experienced any price change on or before the target date, and assign them the fixed default baseline price (10).
  • Combine: Merge both sets using the UNION operator to construct the complete, flawless output table.

6. Constructing SQL Query: Part 1 (Products with Changes On or Before Target Date)

Let's write the first part of our query to fetch products that had at least one price update on or before 2019-08-16.

SELECT 
    product_id, 
    new_price AS price
FROM 
    products
WHERE 
    (product_id, change_date) IN (
        SELECT 
            product_id, 
            MAX(change_date)
        FROM 
            products
        WHERE 
            change_date <= '2019-08-16'
        GROUP BY 
            product_id
    );

How Part 1 Works:

1. Subquery (SELECT product_id, MAX(change_date) ...): Filters all price modifications that occurred on or before 2019-08-16, grouping by product_id to isolate the latest (maximum) change date for each individual product.
2. Tuple Matching (WHERE (product_id, change_date) IN (...)): Matches the exact row in the main table where both the product ID and its change date align with the subquery's most recent date, pulling the corresponding new_price.
3. Limitation: As discovered during our manual dry run, Product #3 has no price changes on or before 2019-08-16. Thus, running only Part 1 will return products 1 and 2, but will miss product 3.

7. Constructing SQL Query: Part 2 (Products with NO Changes Before Target Date)

To capture products like Product #3 that never changed their price prior to the target date, we need to select all product IDs that are not present in our historical subquery and assign them the default price of 10.

SELECT 
    product_id, 
    10 AS price
FROM 
    products
WHERE 
    product_id NOT IN (
        SELECT 
            product_id
        FROM 
            products
        WHERE 
            change_date <= '2019-08-16'
    );

How Part 2 Works:

1. Exclusion Subquery (WHERE product_id NOT IN (...)): Identifies every product_id that did have a price change on or before 2019-08-16.
2. Complement Selection: Selects products from the products table whose IDs are excluded from that subquery list (i.e., products that never changed prices prior to our target date).
3. Hardcoded Baseline: Hardcodes the price column as 10 AS price, fulfilling the problem's assumption.

8. Complete Combined Query & Flow Walkthrough

By combining Part 1 and Part 2 using the UNION operator, we merge both sets of products into a unified, accurate result set.

Final Production SQL Query

-- Part 1: Products that had price changes on or before 2019-08-16
SELECT 
    product_id, 
    new_price AS price
FROM 
    products
WHERE 
    (product_id, change_date) IN (
        SELECT 
            product_id, 
            MAX(change_date)
        FROM 
            products
        WHERE 
            change_date <= '2019-08-16'
        GROUP BY 
            product_id
    )

UNION

-- Part 2: Products that NEVER changed price on or before 2019-08-16 (Default to 10)
SELECT 
    product_id, 
    10 AS price
FROM 
    products
WHERE 
    product_id NOT IN (
        SELECT 
            product_id
        FROM 
            products
        WHERE 
            change_date <= '2019-08-16'
    );

End-to-End Execution Flow

9. Summary and Key Takeaways

• Temporal Window Filtering: When querying state at a specific point in time, always filter records using a threshold condition (change_date <= target_date) before attempting to aggregate.
• Handling Missing States (Default Values): Real-world data often lacks initial state records. When entities start with a default baseline value (10), explicitly account for entities outside your primary filtered window using NOT IN or LEFT JOIN strategies.
• Tuple Comparison in SQL: SQL allows multi-column matching using parentheses (col1, col2) IN (SELECT col1, MAX(col2) ...), which is immensely powerful for fetching exact rows corresponding to aggregate metrics.
• Set Operations (UNION): The UNION operator seamlessly merges distinct logical subsets (historically updated items vs. untouched baseline items) into a single unified result table.
• Index & Primary Key Advantage: Leveraging composite primary keys like (product_id, change_date) ensures fast lookups and guarantees uniqueness when grouping temporal records.