Drill 6 of 15 in Analytics Patterns
0

Analytics Patterns · As-of join · JOIN LATERAL

6. 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…