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

Product Price at a Given Date LeetCode Solution

Problem Statement

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

Output:

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;