Example for joining temporal tables

join_temporal_tables.sql

with table_a AS (

SELECT 1 AS id, DATE('2022-01-01') AS valid_from, DATE('2022-06-01') AS valid_to, 'a1' AS valueA

UNION ALL 

SELECT 1 AS id, DATE('2022-06-01') AS valid_from, DATE('2022-08-01') AS valid_to, 'a2' AS valueA

),

table_b AS (


SELECT 1 AS id, DATE('2022-04-01') AS valid_from, DATE('2022-07-01') AS valid_to, 'b1' AS valueB

UNION ALL 

SELECT 1 AS id, DATE('2022-07-01') AS valid_from, DATE('2022-08-01') AS valid_to, 'b2' AS valueB

UNION ALL

SELECT 1 AS id, DATE('2022-08-01') AS valid_from, DATE('9999-01-01') AS valid_to, 'b3' AS valueB

),


table_c AS (



SELECT 1 AS id, DATE('2022-02-01') AS valid_from, DATE('2022-05-01') AS valid_to, 'c1' AS valueC

UNION ALL 

SELECT 1 AS id, DATE('2022-05-01') AS valid_from, DATE('2022-09-01') AS valid_to, 'c2' AS valueC

UNION ALL

SELECT 1 AS id, DATE('2022-09-01') AS valid_from, DATE('9999-01-01') AS valid_to, 'c3' AS valueC



),



dates AS (

SELECT id, valid_from AS event_date FROM table_a

UNION DISTINCT

SELECT id, valid_to AS event_date FROM table_a

UNION DISTINCT


SELECT id, valid_from AS event_date FROM table_b


UNION DISTINCT

SELECT id, valid_to AS event_date FROM table_b

UNION DISTINCT


SELECT id, valid_from AS event_date FROM table_c


UNION DISTINCT

SELECT id, valid_to AS event_date FROM table_c

),

results AS (


  SELECT 
  d.event_date AS valid_from,
  LEAD(d.event_date, 1) OVER (PARTITION BY d.id ORDER BY d.event_date) AS valid_to, 
  d.id, 
  a.valueA, 
  b.valueB, 
  c.valueC

FROM dates d

LEFT JOIN table_a a ON d.id = a.id AND d.event_date >= a.valid_from AND d.event_date < a.valid_to

LEFT JOIN table_b b ON d.id = b.id AND d.event_date >= b.valid_from AND d.event_date < b.valid_to

LEFT JOIN table_c c ON d.id = c.id AND d.event_date >= c.valid_from AND d.event_date < c.valid_to

QUALIFY valid_to IS NOT NULL


)

SELECT 
  valid_from,
  valid_to,
  id,
  valueA,
  valueB,
  valueC

FROM results