Product Price at a Given Date LeetCode Solution

Last updated on October 9th, 2024 at 10:31 pm

This Leetcode problem Product Price at a Given Date LeetCode Solution is done in SQL.

List of all LeetCode Solution

Level of Question

Medium

Product Price at a Given Date LeetCode Solution

Product Price at a Given Date LeetCode Solution

Problem Statement

Column NameType
product_idint
new_priceint
change_datedate
Table: Products

(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:

product_id new_price change_date
1202019-08-14
2502019-08-14
1302019-08-15
1352019-08-16
2652019-08-17
3202019-08-18
Products table:

Output:

product_idprice
250
135
310

1. Product Price at a Given Date LeetCode Solution MySQL

select 
  i.product_id, 
  max(
    if(
      i.product_id not in (
        select 
          product_id 
        from 
          Products 
        where 
          change_date <= date '2019-08-16' 
        group by 
          product_id
      ), 
      10, 
      (
        select 
          new_price 
        from 
          Products 
        where 
          product_id = i.product_id 
          and product_id = q.product_id 
          and change_date = q.max_change_date
      )
    )
  ) as price 
from 
  (
    select 
      distinct product_id 
    from 
      Products
  ) as i, 
  (
    select 
      product_id, 
      max(change_date) as max_change_date 
    from 
      Products 
    where 
      change_date <= date '2019-08-16' 
    group by 
      product_id
  ) as q 
group by 
  i.product_id;
Scroll to Top