1164. Product Price at a Given Date

SQL 50 · Advanced Select and Joins (day 5). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.

MediumSolve it on LeetCode ↗

Your work

Status Notes Your solution (MySQLPostgreSQL)

Hints

Open one at a time. Try to finish with as few as you can.

1. What does one output row represent?

One product.

2. Approach

Latest change on or before 2019-08-16 per product (MAX(change_date) with tuple IN). Products with no such change get 10: LEFT JOIN from the distinct products, then fill the gap.

3. The trap

Products whose first change is after the date must still appear, with price 10.

Reference solution

Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.

Show the MySQLPostgreSQL solution

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

Patterns used

Which join · Phrase finder