Product Price on a Specific Date

A product's price is whatever the most recent change said it was as of the cut-off date — so the whole problem reduces to: for each product, find the row with the maximum effective_date <= '2019-08-16' and read its new_price, falling back to 10 when no such row exists.

Problem

Table PriceChanges(product_id, new_price, effective_date); (product_id, effective_date) is the primary key. Each row says "on this date the price became new_price." Report every product's price on 2019-08-16. A product with no change on or before that date is assumed to cost 10. Order by product_id.

Input

PriceChanges
+------------+-----------+----------------+
| product_id | new_price | effective_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   |   <- after cutoff
|     3      |    20     |   2019-08-18   |   <- after cutoff
+------------+-----------+----------------+

Expected output

+------------+-------+
| product_id | price |
+------------+-------+
|     1      |  35   |   latest change on/before 08-16
|     2      |  50   |   08-17 change is in the future
|     3      |  10   |   no change on/before 08-16 -> default
+------------+-------+

The mechanism: "latest row per group" + a default

Two sub-jobs are hiding here, and seeing them separately is the whole insight:

1. Driving set. The answer has one row per product that exists — not per price change. Product 3's only change (08-18) is filtered out by the cut-off, yet product 3 must still appear with price 10. So you cannot derive the row set from the rows that survive the date filter; you need the full universe of products first. That is why SELECT DISTINCT product_id is the outer driver, not the filtered table.
2. Pick the winning row. Within each product's history, keep only changes with effective_date <= '2019-08-16', then take the one with the largest date. COALESCE(..., 10) patches the hole when that set is empty.

Solution A — correlated subquery + COALESCE

This is the most direct transcription of the mechanism: drive off the distinct products, and for each one run a tiny "latest price" lookup.

SELECT p.product_id,
       COALESCE(
         (SELECT pc.new_price
          FROM PriceChanges pc
          WHERE pc.product_id = p.product_id
            AND pc.effective_date <= '2019-08-16'
          ORDER BY pc.effective_date DESC
          LIMIT 1),
         10
       ) AS price
FROM (SELECT DISTINCT product_id FROM PriceChanges) p
ORDER BY p.product_id;

It is correct, but be honest about its cost. The inner SELECT ... ORDER BY ... LIMIT 1 is correlated — it re-runs once per driving row. With N distinct products it fires N times; each execution scans/sorts that product's history. With an index on (product_id, effective_date) each lookup is a cheap index seek + backward scan of one row, so it's fine at small scale. Without that index, each iteration is a full-table scan and the query degrades toward O(N · rows) — the classic correlated-subquery trap.

Solution B — one pass with ROW_NUMBER() (the window alternative)

Instead of N independent lookups, rank every qualifying row once and keep rank 1 per product. One scan, one sort, no correlation.

SELECT pids.product_id,
       COALESCE(latest.new_price, 10) AS price
FROM (SELECT DISTINCT product_id FROM PriceChanges) pids
LEFT JOIN (
    SELECT product_id, new_price
    FROM (
        SELECT product_id, new_price,
               ROW_NUMBER() OVER (
                 PARTITION BY product_id
                 ORDER BY effective_date DESC
               ) AS rn
        FROM PriceChanges
        WHERE effective_date <= '2019-08-16'
    ) ranked
    WHERE rn = 1
) latest ON latest.product_id = pids.product_id
ORDER BY pids.product_id;

Note the structure: the window-ranked table only contains products that have a qualifying row, so it cannot stand alone as the result — product 3 vanishes from it. The LEFT JOIN back onto the full distinct-product set re-introduces product 3 as a NULL, and COALESCE(..., 10) fills it. Same two sub-jobs as Solution A, just reorganized so the database does the "latest per group" work in a single pass instead of per row.

Worked trace (Solution A)

Driving set from SELECT DISTINCT product_id: {1, 2, 3}. For each, the correlated subquery runs:

Final, ordered by product_id: (1, 35), (2, 50), (3, 10).

Pitfalls

• Driving off the filtered table. If you write FROM (SELECT DISTINCT product_id FROM PriceChanges WHERE effective_date <= '2019-08-16'), product 3 disappears entirely — its only change is in the future, so it has no qualifying row and never reaches the result. The default branch never fires. The distinct set must come from the unfiltered table.
• Using MAX(new_price) instead of the latest row. A tempting SELECT MAX(new_price) WHERE date <= cutoff picks the highest price, not the most recent. If a product's price dropped over time, you'd report a stale-but-larger number. The tie-break is the date, never the price.
• Tie on effective_date. The primary key (product_id, effective_date) guarantees no two changes share a date for one product, so ORDER BY effective_date DESC LIMIT 1 is deterministic here. In a schema without that uniqueness, both the subquery and ROW_NUMBER() would pick an arbitrary row among the ties — add a tiebreaker column.
• Missing the composite index. Solution A looks innocent but without an index on (product_id, effective_date) it is N full scans. On large tables this is where it dies; reach for Solution B or add the index.
• GROUP BY + correlated MAX(date) anti-join. Some write a self-join matching each row to MAX(effective_date) per product. It works but materializes a per-group aggregate and re-joins; ROW_NUMBER() expresses the same intent in one pass and is easier to read.

Takeaways

• "Value as of a date" = latest row per group with date <= cutoff; the default is a separate COALESCE concern, not part of the ranking.
• The result set is one row per entity that exists, so derive the driving set from the unfiltered universe and LEFT JOIN the filtered facts back onto it.
• A correlated ORDER BY ... LIMIT 1 reads clearly but runs once per driving row — index (product_id, effective_date) or switch to ROW_NUMBER() for one-pass behavior on large data.
• Rank by the date, never MAX(price) — "most recent" and "largest" are not the same row.

Based on LeetCode 1164 "Product Price at a Given Date" (Database, Medium). Mechanism, cost analysis, and the ROW_NUMBER()/window alternative drawn from the PostgreSQL and MySQL window-function docs and Markus Winand's SQL Performance Explained (correlated-subquery cost, latest-row-per-group patterns). Re-authored and deepened for this guide — added the per-row cost discussion, the window-function solution, the driving-set rationale, a worked trace, and the failure modes.