[SQL문제풀기] Product Price at a Given Date

Nov 10, 2025

Contents

문제

Product Price at a Given Date - LeetCode

Can you solve this real interview question? Product Price at a Given Date - Table: Products +---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | new_price | int | | change_date | date | +---------------+---------+ (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. Initially, all products have price 10. Write a solution to find the prices of all products on the date 2019-08-16. Return the result table in any order. The result format is in the following example. Example 1: Input: Products table: +------------+-----------+-------------+ | product_id | new_price | change_date | +------------+-----------+-------------+ | 1 | 20 | 2019-08-14 | | 2 | 50 | 2019-08-14 | | 1 | 30 | 2019-08-15 | | 1 | 35 | 2019-08-16 | | 2 | 65 | 2019-08-17 | | 3 | 20 | 2019-08-18 | +------------+-----------+-------------+ Output: +------------+-------+ | product_id | price | +------------+-------+ | 2 | 50 | | 1 | 35 | | 3 | 10 | +------------+-------+

https://leetcode.com/problems/product-price-at-a-given-date/description/?envType=study-plan-v2&envId=top-sql-50

MySQL

내가 작성한 오답

: 가격이 오르는 경우만 생각했다


select i.product_id, ifnull(p.price, 10) price
from (select product_id, max(new_price) price
        from Products
        where date_format(change_date,'%Y-%m-%d') <= '2019-08-16'
        group by product_id) p
right join (select distinct product_id from Products) i
on p.product_id = i.product_id;

내가 작성한 정답


with a as(select product_id, new_price price
            from Products p
            where (p.product_id, p.change_date) in (select product_id, max(change_date)
                                                from Products
                                                where date_format(change_date,'%Y-%m-%d') <= '2019-08-16'
                                                group by product_id))
select product_id, price
from a
union all
select distinct product_id, 10 price
from Products
where product_id not in (select product_id from a);