Point in Time Joins

The query below lists each insurance claim together with the plan tier that the customer held on the day of the claim. It reads two tables: claims, with one row per paid claim, and plan_history, with one row per version of a customer's plan.

SELECT c.claim_id, c.customer_id, c.day_no, p.plan_key, p.tier
FROM claims c
JOIN plan_history p
  ON p.customer_id = c.customer_id
 AND c.day_no >= p.valid_from_day
 AND (c.day_no < p.valid_to_day OR p.valid_to_day IS NULL)
ORDER BY c.claim_id;
Copy

Output:

+----------+-------------+--------+----------+---------+
Copy