Point-in-time price lookup

products.price is today’s price. An order from last year was placed at last year’s price. The bitshop price_changes table keeps the history: one row per change, effective from changed_at until the next change for that product.

Write price_at(price_changes, lookups):

• price_changes has columns product_id, price, changed_at (dates as YYYY-MM-DD strings).
• lookups has columns product_id, date (also YYYY-MM-DD strings). Rows can repeat and come in any order.
• For each lookup row, find that product’s most recent change with changed_at <= date. A change dated on the lookup day already applies.
• If the date is before the product’s first change, or the product has no history, the price is missing (NaN or None).
• Return a DataFrame with columns product_id, date, price, one row per lookup row, in the same order as lookups. Keep date as the original string.

A plain merge on product_id gives one row per change. You need exactly one per lookup.

>>> price_at(price_changes, pd.DataFrame({"product_id": [1, 1], "date": ["2024-05-10", "2024-05-11"]}))
   product_id        date  price
0           1  2024-05-10  26.42
1           1  2024-05-11  29.63

Approach

An as-of join. Tag each lookup with its position, sort both sides by time, join each lookup to the latest change at or before it within the same product, then restore the original order.

import pandas as pd


def price_at(price_changes, lookups):
    left = lookups[["product_id", "date"]].copy()
    left["_row"] = range(len(left))
    left["_ts"] = pd.to_datetime(left["date"])
    right = price_changes[["product_id", "price", "changed_at"]].copy()
    right["_ts"] = pd.to_datetime(right["changed_at"])
    merged = pd.merge_asof(
        left.sort_values("_ts"),
        right.sort_values("_ts")[["product_id", "_ts", "price"]],
        on="_ts",
        by="product_id",
        direction="backward",
    )
    merged = merged.sort_values("_row").reset_index(drop=True)
    return merged[["product_id", "date", "price"]]

direction="backward" includes an exact match, so a change dated on the lookup day applies. Lookups with no earlier change get NaN.

The alternative is a plain merge on product_id, a filter on changed_at <= date, then keeping the last row per lookup. It gives the same answer but builds every (lookup, change) pair first. With a long price history that intermediate table gets big.

Complexity

Sorting is O(L log L + C log C) for L lookups and C changes. The as-of join itself is one merge-style pass over both sorted sides.

Why this matters

This is a point-in-time join, and it’s the core of slowly changing dimensions in a warehouse: attribute each fact to the version of the dimension that was true when it happened. ML feature stores do the same thing to avoid leakage. A training row from March must only see features computed before March, or the model learns from the future and then disappoints in production. Joining on “latest” instead of “as of” is one of the most common silent bugs in analytics.