1164. Product Price at a Given Date - Detailed Explanation

Problem Statement

Table Products:

+-------------+---------+-------------+  
| Column Name | Type    |  
+-------------+---------+-------------+  
| product_id  | int     |  
| new_price   | int     |  
| change_date | date    |  
+-------------+---------+  

• (product_id, change_date) is the primary key.
• Each row records that at change_date, the product’s price became new_price.
  Write a query to report the price of every product on 2019-08-16. If a product has no price change on or before that date, its price is 10. Return (product_id, price) in any order citeturn0search2.

Examples

Given:

Products  
+------------+-----------+-------------+  
| 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    |  
+------------+-------+  

Approach 1 — Subquery + Join

1. List all distinct products in T.
2. In P, for each product find the max change_date ≤ '2019-08-16' and take its new_price.
3. Left join T with P, using COALESCE (or IFNULL) to default missing prices to 10.

WITH
  T AS (
    SELECT DISTINCT product_id
    FROM Products
  ),
  P AS (
    SELECT
      product_id,
      new_price AS price
    FROM Products
    WHERE (product_id, change_date) IN (
      SELECT
        product_id,
        MAX(change_date) AS change_date
      FROM Products
      WHERE change_date <= '2019-08-16'
      GROUP BY product_id
    )
  )
SELECT
  T.product_id,
  COALESCE(P.price, 10) AS price
FROM T
LEFT JOIN P
  ON T.product_id = P.product_id;

Approach 2 — Window Function

1. In Ranked, keep only changes on or before '2019-08-16' and rank them per product by change_date DESC.
2. In Latest, select rows where rn = 1 (the latest change).
3. Left join all distinct products with Latest, defaulting to 10 if missing.

WITH Ranked AS (
  SELECT
    product_id,
    new_price,
    ROW_NUMBER() OVER (
      PARTITION BY product_id
      ORDER BY change_date DESC
    ) AS rn
  FROM Products
  WHERE change_date <= '2019-08-16'
),
Latest AS (
  SELECT product_id, new_price AS price
  FROM Ranked
  WHERE rn = 1
)
SELECT
  p.product_id,
  COALESCE(L.price, 10) AS price
FROM (
  SELECT DISTINCT product_id
  FROM Products
) AS p
LEFT JOIN Latest AS L
  ON p.product_id = L.product_id;

Complexity Analysis

• Time: O(N log N) due to sorting/grouping by change_date for N rows.
• Space: O(N) for intermediate CTEs and indexes.