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…