Canteen Menu Prices on 15 March — SQL Medium Problem

Problem statement

Every item on the hostel canteen's menu cost ₹10 when the menu was launched. Since then, each price change has been logged with the date it took effect; a change stays in force until the item's next change.

For every item that appears in the log, return its price on 2025-03-15, with columns item_id and price. A change that takes effect on 2025-03-15 already applies that day; an item whose first change is later than that still costs 10. Return the rows in any order.

Tables

Table: MenuPriceChange

Primary key: item_id, effective_on.

One row per price change: from effective_on onwards the item costs new_price rupees, until its next change.

Examples

Example 1

MenuPriceChange

Output

How to solve Canteen Menu Prices on 15 March

The price in force on a day is the new_price of the item's most recent change on or before that day. Changes after 2025-03-15 are irrelevant, and an item with no change by then still has the launch price of 10.

A correlated scalar subquery says exactly that. Start from the distinct items (so an item whose only changes are in the future still gets a row), and for each one look up its changes with effective_on <= '2025-03-15', newest first, LIMIT 1. If there is none, the scalar subquery is NULL and COALESCE(…, 10) supplies the default. The <= is what makes a change dated on the 15th count. Dates are compared as text, which works because they are all YYYY-MM-DD.

Two other plans give the same answer. A set-based one finds each item's latest qualifying date with MAX(effective_on) grouped by item, joins back to read the price (here with a row-value IN), and adds the default-price items with UNION ALL — those are the items whose earliest change is after the date. A window version ranks each item's qualifying changes with ROW_NUMBER() newest first and LEFT JOINs rank 1 onto the item list. With an index on (item_id, effective_on), each lookup in the correlated version is a short index seek.

Reference solution (MySQL)

SELECT i.item_id,
       COALESCE((SELECT p.new_price
                 FROM MenuPriceChange p
                 WHERE p.item_id = i.item_id AND p.effective_on <= '2025-03-15'
                 ORDER BY p.effective_on DESC
                 LIMIT 1), 10) AS price
FROM (SELECT DISTINCT item_id FROM MenuPriceChange) i

Another way

SELECT p.item_id, p.new_price AS price
FROM MenuPriceChange p
WHERE (p.item_id, p.effective_on) IN (
  SELECT item_id, MAX(effective_on) FROM MenuPriceChange
  WHERE effective_on <= '2025-03-15' GROUP BY item_id
)
UNION ALL
SELECT item_id, 10 FROM MenuPriceChange GROUP BY item_id HAVING MIN(effective_on) > '2025-03-15'

Another way

SELECT i.item_id, COALESCE(r.new_price, 10) AS price
FROM (SELECT DISTINCT item_id FROM MenuPriceChange) i
LEFT JOIN (
  SELECT item_id, new_price, ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY effective_on DESC) AS rn
  FROM MenuPriceChange WHERE effective_on <= '2025-03-15'
) r ON r.item_id = i.item_id AND r.rn = 1