Join 2 SCD tables

WITH
set_a AS (
SELECT
    '1900-01-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    1 AS value,
    56 as hidden_value
UNION ALL
SELECT
    '2020-09-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    2 AS value,
    57 as hidden_value
UNION ALL
SELECT
    '2021-01-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    2 AS value,
    58 as hidden_value
UNION ALL
SELECT
    '2015-01-01'::DATE AS _valid_from_date_time,
    'b' AS primary_key,
    10 AS value,
    58 as hidden_value
),

set_b AS (
SELECT
    '2000-01-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    1 AS value
UNION ALL
SELECT
    '2020-06-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    2 AS value
UNION ALL
SELECT
    '2022-01-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    3 AS value
UNION ALL
SELECT
    '1980-01-01'::DATE AS _valid_from_date_time,
    'b' AS primary_key,
    10 AS value
UNION ALL
SELECT
    '2022-01-01'::DATE AS _valid_from_date_time,
    'b' AS primary_key,
    20 AS value
),

set_c AS (
SELECT
    '2014-01-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    1 AS value
UNION ALL
SELECT
    '2026-01-01'::DATE AS _valid_from_date_time,
    'a' AS primary_key,
    2 AS value
UNION ALL
SELECT
    '2026-01-01'::DATE AS _valid_from_date_time,
    'b' AS primary_key,
    55 AS value
),

set_a_date AS (
SELECT
   _valid_from_date_time,
   IFNULL(LEAD(_valid_from_date_time) OVER (PARTITION BY primary_key ORDER BY _valid_from_date_time),'2999-12-31') AS _valid_to_date_time,
   primary_key,
   value,
    hidden_value
FROM
    set_a
),

set_b_date AS (
SELECT
   _valid_from_date_time,
   IFNULL(LEAD(_valid_from_date_time) OVER (PARTITION BY primary_key ORDER BY _valid_from_date_time),'2999-12-31') AS _valid_to_date_time,
   primary_key,
   value
FROM
    set_b
),

set_c_date AS (
SELECT
   _valid_from_date_time,
   IFNULL(LEAD(_valid_from_date_time) OVER (PARTITION BY primary_key ORDER BY _valid_from_date_time),'2999-12-31') AS _valid_to_date_time,
   primary_key,
   value
FROM
    set_c
),

key_dates AS (
  SELECT DISTINCT _valid_from_date_time, primary_key FROM set_a_date
  UNION
  SELECT DISTINCT _valid_from_date_time, primary_key FROM set_b_date
  UNION
  SELECT DISTINCT _valid_from_date_time, primary_key FROM set_c_date
)


SELECT
   key_dates._valid_from_date_time,
   LEAD(key_dates._valid_from_date_time, 1, '2999-12-31') OVER (PARTITION BY key_dates.primary_key ORDER BY key_dates._valid_from_date_time) AS _valid_to_date_time,
   set_a_date._valid_from_date_time AS a_from_date,
   set_a_date._valid_to_date_time AS a_to_date,
   set_b_date._valid_from_date_time AS b_from_date,
   set_b_date._valid_to_date_time AS b_to_date,
   set_c_date._valid_from_date_time AS c_from_date,
   set_c_date._valid_to_date_time AS c_to_date,
   set_a_date.value AS a_value,
   set_b_date.value AS b_value,
   set_c_date.value AS c_value,
   COALESCE(set_a_date.primary_key,set_b_date.primary_key,set_c_date.primary_key) AS primary_key
FROM key_dates
LEFT JOIN set_a_date
    ON key_dates._valid_from_date_time >= set_a_date._valid_from_date_time
    AND key_dates._valid_from_date_time < set_a_date._valid_to_date_time
    AND key_dates.primary_key = set_a_date.primary_key
LEFT JOIN set_b_date
    ON key_dates._valid_from_date_time >= set_b_date._valid_from_date_time
    AND key_dates._valid_from_date_time < set_b_date._valid_to_date_time
    AND key_dates.primary_key = set_b_date.primary_key
LEFT JOIN set_c_date
    ON key_dates._valid_from_date_time >= set_c_date._valid_from_date_time
    AND key_dates._valid_from_date_time < set_c_date._valid_to_date_time
    AND key_dates.primary_key = set_c_date.primary_key
ORDER BY primary_key, _valid_from_date_time