SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

Analytics Patterns · As-of join · JOIN LATERAL

51. Plan at time of purchase

Concept: As-of join · JOIN LATERALshow

An as-of (point-in-time) join attaches the version of a row that was in effect at a moment: the latest change on or before the event's date, not the latest change overall. In Postgres, JOIN LATERAL runs a small subquery per row, so you can filter to earlier changes, ORDER BY date DESC and LIMIT 1.

Example (different data):

SELECT s.sold_on, s.sku, p.price
FROM sales s
JOIN LATERAL (
  SELECT price FROM price_history h
  WHERE h.sku = s.sku AND h.valid_from <= s.sold_on
  ORDER BY h.valid_from DESC
  LIMIT 1
) AS p ON true;

Your task

Finance books every purchase against the plan the customer was on that day, even if they changed plans since.

For every purchase event, return user_id, purchase_date (the event_date) and plan (the user's most recent plan change on or before that date; a change on the purchase day counts). Sort by user_id.

Starting Postgres in your browser…

Checking your access…