Product Price at a Given Date - Practice Coding Problems

Product Price at a Given Date - Problem

Database Medium

Product Price at a Given Date

You are working as a data analyst for an e-commerce platform that tracks product price changes over time. The company maintains a Products table that records every price change for every product.

Table Structure:
Products table:
• product_id (int): Unique identifier for each product
• new_price (int): The updated price of the product
• change_date (date): The date when the price was changed

Note: The combination of (product_id, change_date) is unique, meaning each product can only have one price change per day.

Important Business Rule: All products start with an initial price of $10 before any price changes are recorded.

Your Task:
Find the price of all products on the specific date 2019-08-16. For products that had no price changes by this date, return the default price of $10. For products that had price changes, return the most recent price as of this date.

Output: Return a result table with product_id and price columns in any order.

Input & Output

example_1.sql — Basic Price Changes

$ Input: Products table: +------------+-----------+-------------+ | product_id | new_price | change_date | +------------+-----------+-------------+ | 1 | 20 | 2019-08-14 | | 2 | 50 | 2019-08-11 | | 1 | 30 | 2019-08-15 | | 1 | 35 | 2019-08-16 | | 3 | 20 | 2019-08-17 | +------------+-----------+-------------+

› Output: +------------+-------+ | product_id | price | +------------+-------+ | 1 | 35 | | 2 | 50 | | 3 | 10 | +------------+-------+

💡 Note: Product 1: Latest price change on or before 2019-08-16 is 35 (on 2019-08-16). Product 2: Latest price change on or before 2019-08-16 is 50 (on 2019-08-11). Product 3: No price changes on or before 2019-08-16, so default price 10 is used.

example_2.sql — All Default Prices

$ Input: Products table: +------------+-----------+-------------+ | product_id | new_price | change_date | +------------+-----------+-------------+ | 1 | 25 | 2019-08-17 | | 2 | 40 | 2019-08-18 | | 3 | 60 | 2019-08-19 | +------------+-----------+-------------+

› Output: +------------+-------+ | product_id | price | +------------+-------+ | 1 | 10 | | 2 | 10 | | 3 | 10 | +------------+-------+

💡 Note: All price changes occurred after 2019-08-16, so all products should have the default price of 10 on the target date.

example_3.sql — Mixed Scenarios

$ Input: Products table: +------------+-----------+-------------+ | product_id | new_price | change_date | +------------+-----------+-------------+ | 1 | 100 | 2019-08-10 | | 1 | 120 | 2019-08-16 | | 2 | 80 | 2019-08-20 | | 3 | 90 | 2019-08-15 | +------------+-----------+-------------+

› Output: +------------+-------+ | product_id | price | +------------+-------+ | 1 | 120 | | 2 | 10 | | 3 | 90 | +------------+-------+

💡 Note: Product 1: Has two changes before/on target date, latest is 120. Product 2: Only change is after target date, so default 10. Product 3: Has one change before target date, so price is 90.

Constraints

1 ≤ Products table rows ≤ 10⁴
1 ≤ product_id ≤ 5000
0 ≤ new_price ≤ 10⁶
change_date is between 2019-01-01 and 2019-12-31
(product_id, change_date) is unique - no duplicate price changes on same date

Visualization

Tap to expand

Understanding the Visualization

Timeline Setup

Create a timeline for each product showing all price changes

Target Date Line

Draw a vertical line at 2019-08-16

Find Latest Price

For each product, find the most recent price change before or on the target date

Apply Defaults

Products with no changes before the target date get the default price of $10

Key Takeaway

🎯 Key Insight: Use window functions to rank price changes by date for each product, then select the most recent valid price. Products without changes before the target date automatically get the default price of $10.

Asked in

a Amazon 35 G Google 28 ⊞ Microsoft 22 f Meta 15

The optimal solution uses window functions with ROW_NUMBER() to efficiently rank price changes by date for each product. The key insight is to partition by product_id and order by change_date DESC to find the most recent price change before or on the target date. Products without price changes are handled by a UNION that assigns the default price of $10. This approach achieves O(n log n) time complexity with a single table scan.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function Approach (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() window function to rank prices by date for efficient lookup
Brute Force (Correlated Subqueries)	O(n²)	O(1)	Use correlated subqueries to find the latest price for each product

Window Function Approach (Optimal) — Algorithm Steps

Filter records to only include changes on or before '2019-08-16'
Use ROW_NUMBER() window function to rank prices by date (descending) for each product
Select only the rank 1 (most recent) record for each product
UNION with products that have no price changes, assigning default price of 10

Visualization

Tap to expand

Step-by-Step Walkthrough

Filter by Date

Filter records where change_date <= '2019-08-16'

Partition and Rank

Use ROW_NUMBER() to rank prices by date for each product

Select Latest

Select only rank 1 (most recent) records

Add Defaults

UNION products with no changes, using default price 10

Code -

solution.c — C

#include <stdio.h>

int main() {
    printf(
        "WITH RankedPrices AS (\n"
        "    SELECT \n"
        "        product_id,\n"
        "        new_price,\n"
        "        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY change_date DESC) as rn\n"
        "    FROM Products \n"
        "    WHERE change_date <= '2019-08-16'\n"
        ")\n"
        "SELECT product_id, new_price as price\n"
        "FROM RankedPrices \n"
        "WHERE rn = 1\n\n"
        "UNION\n\n"
        "SELECT DISTINCT product_id, 10 as price\n"
        "FROM Products \n"
        "WHERE product_id NOT IN (\n"
        "    SELECT DISTINCT product_id \n"
        "    FROM Products \n"
        "    WHERE change_date <= '2019-08-16'\n"
        ")\n"
    );
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

O(n log n) due to the implicit sorting in the window function, but much more efficient than nested loops

⚡ Linearithmic

Space Complexity

O(n)

Space needed for the window function results and final output

⚡ Linearithmic Space

78.0K Views

High Frequency

~25 min Avg. Time

1.9K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Window Function Approach (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler