ml-002 · As-of price joins — R

Problem statement

Open this practice in GitHub Codespaces runs in your browser; open this folder under practices/ after it starts

A retailer records every order in an orders data frame (order_id, product_id, order_datetime) and keeps prices in a price_history data frame (product_id, price, effective_from) with one row per price change. Reconstruct the price each order was actually billed at: the most recent effective_from at or before order_datetime for that product. First demonstrate why a naive join on product_id is wrong (it duplicates orders and can attach prices that only took effect after the order), then produce the correct one-row-per-order result with dplyr (filter + slice_max per order, re-joined to the full orders table), and report how many orders have no price in effect yet. All data is generated in-script with base R RNG (fixed seed) — about 400 orders, 60 products, 1–5 price changes each.

Learning objectives

After completing this practice you should be able to:

  • explain why an equality join cannot express “price in effect at time t” and what two distinct errors it causes (row duplication, future-price leakage)
  • implement a grouped as-of join in dplyr with inner_join + filter(effective_from <= order_datetime) + slice_max(effective_from, n = 1) per order
  • use relationship = "many-to-many" deliberately and understand the warning it silences
  • handle orders placed before a product’s first price without silently dropping them

Concepts and definitions

See concepts for the shared background. R-specific notes: dplyr has no built-in as-of join, so the practice builds one from primitives (data.table users get roll = TRUE in X[Y, on = ..., roll = TRUE]). Since dplyr 1.1, left_join/inner_join warn on unexpected many-to-many matches unless you declare relationship = "many-to-many" — here the blow-up is intentional (candidate generation), so declaring it documents intent. Compare POSIXct timestamps in a single timezone (this practice uses UTC).

Input / output description

Item Description
Input orders: 400 rows — order_id (chr), product_id (chr), order_datetime (POSIXct, UTC). price_history: ~175 rows — product_id (chr), price (dbl), effective_from (POSIXct, UTC), 1–5 rows per product. Both built in-script with set.seed(42).
Output One row per order with the effective price and its effective_from (NA when no price was in effect yet), plus printed diagnostics: naive-join row count, count of future-price rows, no-price-yet order count, and correct vs shortcut revenue.

Before-coding checklist

My attempt

Work here. Run cells interactively; nothing below overwrites this section.

# Milestone 1: generate the synthetic tables (set.seed(42), ~400 orders,
# 60 products, 1-5 price rows each; make some products first priced mid-year)
# Milestone 2: naive left_join on product_id; count the extra rows and the
# rows whose effective_from is AFTER order_datetime
# Milestone 3: correct as-of join - join candidates, filter to prices already
# in effect, slice_max(effective_from) per order_id
# Milestone 4: re-join to the full orders table so no-price-yet orders keep
# a row with NA price; verify one row per order and no future prices
# Milestone 5: report orders with no price yet, and correct revenue vs the
# "latest price per product" shortcut

Hint 1

Think about what a single order for a product with four price rows becomes after left_join(orders, price_history, by = "product_id") — four rows, only one of which can be right, and some of which may not even have existed yet at order time. You need a matching rule that is an inequality on time within each product, then a way to keep only the latest qualifying row per order.

Hint 2

inner_join(orders, price_history, by = "product_id", relationship = "many-to-many") |> filter(effective_from <= order_datetime) |> group_by(order_id) |> slice_max(effective_from, n = 1, with_ties = FALSE). Then left_join that result back onto the full orders table by order_id — otherwise orders placed before their product’s first price disappear instead of surfacing as NA.

Complete solution

The official, tested script lives at solutions/solution.R. Run it with:

Rscript practices/ml-002-asof-price-join/solutions/solution.R

Core of the solution (data generation and diagnostics omitted here):

asof_price_join <- function(orders, price_history) {
  candidates <- dplyr::inner_join(orders, price_history, by = "product_id",
                                  relationship = "many-to-many") |>
    dplyr::filter(effective_from <= order_datetime) |>
    dplyr::group_by(order_id) |>
    dplyr::slice_max(effective_from, n = 1, with_ties = FALSE) |>
    dplyr::ungroup()
  orders |>
    dplyr::left_join(
      dplyr::select(candidates, order_id, price, effective_from),
      by = "order_id"
    ) |>
    dplyr::arrange(order_id)
}

With seed 42 the script reports: naive join 1147 rows (x2.87 duplication) with 342 future-price rows; as-of join 400 rows with 7 no-price-yet orders; correct revenue 27218.25 vs 28811.95 for the latest-price shortcut. (R’s RNG differs from numpy’s, so the exact figures differ from the Python page while every qualitative conclusion is identical.)

Line-by-line explanation

  • inner_join(..., relationship = "many-to-many"): builds every (order, price version) candidate pair for the same product. The relationship argument declares the row explosion as intentional, silencing dplyr’s many-to-many warning without hiding real bugs elsewhere.
  • filter(effective_from <= order_datetime): discards future prices; <= keeps a price that takes effect at the exact order timestamp.
  • group_by(order_id) |> slice_max(effective_from, n = 1, with_ties = FALSE): keeps the single most recent qualifying price per order. with_ties = FALSE guarantees exactly one row even if two candidates tied on the timestamp.
  • left_join(orders, ...) at the end: the filter deleted all candidates for orders placed before the product’s first price, so joining back to the full orders table restores those rows with NA price rather than dropping them.
  • arrange(order_id): a stable, predictable row order for comparisons and printing.
  • In main(), stopifnot(all(matched$effective_from <= matched$order_datetime)) is a runtime guarantee that no future price ever slipped through.

Design decisions

  • Primitives over a specialized package. dplyr has no merge_asof equivalent, and the filter + slice_max pattern is transparent and testable; for large data the idiomatic upgrade is data.table’s rolling join.
  • inner_join for candidates, left_join for the final result. The inner join keeps the candidate table minimal; the closing left join is what preserves no-price-yet orders — the separation makes each step’s job explicit.
  • with_ties = FALSE so the invariant “exactly one row per order” holds by construction, not by luck of the data.
  • UTC POSIXct everywhere. Mixing timezones (or naive Date vs POSIXct) in the comparison would silently shift the boundary cases.
  • Deliberately adversarial synthetic data. Late-priced products and multiple revisions per product guarantee both failure modes (duplication, future prices) actually occur, so the diagnostics are non-trivial.

Common mistakes

  • Stopping after slice_max without re-joining to orders — orders with no eligible price silently vanish and the result has fewer rows than orders.
  • Using slice_max(price, ...) instead of slice_max(effective_from, ...) — picks the most expensive qualifying price, not the most recent.
  • Filtering with < instead of <=, mispricing orders placed at the exact moment a price takes effect.
  • Deduplicating the naive join with distinct(order_id, .keep_all = TRUE) — which row survives depends on row order, not on temporal correctness.
  • Ignoring (or suppressing globally) the many-to-many warning instead of declaring relationship = "many-to-many" on the one join where it is intended.

Alternative solution

With data.table, an as-of join is native and far more efficient — no candidate blow-up:

library(data.table)
ph <- as.data.table(price_history)
od <- as.data.table(orders)
ph[, join_time := effective_from]
od[, join_time := order_datetime]
result <- ph[od, on = .(product_id, join_time), roll = TRUE]
# roll = TRUE rolls the last observation (price) forward to the order time

roll = TRUE implements exactly “most recent at or before”. Prefer it when price history is large; prefer the dplyr version when the team’s codebase is tidyverse-first and data sizes are moderate. A base-R route also exists: split the history by product and findInterval(order_datetime, effective_from) per group — fast, but more bookkeeping.

Extensions and harder variants

  • Add an effective_to column instead (validity intervals) and implement the join with dplyr::join_by(product_id, between(order_datetime, effective_from, effective_to)).
  • Add promotional prices in a second table and resolve precedence (promo wins over list price when both are in effect).
  • Scale to 10M orders: compare the dplyr candidate-pair approach with data.table rolling joins for time and memory.
  • Make order timestamps local-time (America/New_York) while prices stay UTC, and show what breaks around DST transitions.
  • Backfill policy: for no-price-yet orders, attach the first price instead (a forward as-of join) and discuss when that is defensible.

Review questions

Answer out loud before expanding.

Each order matches every price row of its product, so an order for a product with k price versions contributes k rows. Summing price over that result adds each order’s product prices across all versions — it multiple-counts orders and mixes past and future prices, so the total is not revenue under any definition.

It declares that multiple matches on both sides are expected, silencing the warning dplyr otherwise emits when a join unexpectedly multiplies rows. On the candidate join the multiplication is the algorithm (every price version is a candidate). Elsewhere, the warning usually signals an unnoticed duplicate key — exactly the bug this practice is about — so it should be investigated, not silenced.

The new price: it satisfies the <= filter and has the largest qualifying effective_from, so slice_max selects it. “Effective from” means the price is in force at that instant. Using < would bill the old price for exactly-boundary orders — an off-by-one that is tiny in aggregate and painful in audits.

The inequality filter removes all candidate rows for an order placed before its product’s first price, so that order is absent from the grouped result. Left-joining from orders restores it with NA price, keeping the row count at one per order and making the missing-price cases visible for an explicit downstream decision instead of a silent drop.

It guarantees exactly one row per group even when several candidates share the maximum effective_from (e.g. two price rows loaded with identical timestamps). Without it, ties return multiple rows, the “one row per order” invariant breaks, and the subsequent left join would duplicate those orders — quietly reintroducing the very bug the practice set out to fix.

Personal reflection

What was hard? What would you do differently? Date your entries.