join_test2.sql

with
  tbl1 as (
    select 1 as tbl1_id, 'hoge1' as name, '2020-01-01' as created_at
    union all
    select 2 as tbl1_id, 'hoge2' as name, '2020-01-01' as created_at
  ),
  tbl2 as (
    select 1 as tbl1_id, 11 as tbl2_id, 'fuga1-1' as name, '2020-02-01' as created_at
    union all
    select 1 as tbl1_id, 12 as tbl2_id, 'fuga1-2' as name, '2020-02-02' as created_at
    union all
    select 1 as tbl1_id, 13 as tbl2_id, 'fuga1-3' as name, '2020-02-03' as created_at
    union all
    select 2 as tbl1_id, 21 as tbl2_id, 'fuga2-1' as name, '2020-02-02' as created_at
    union all
    select 2 as tbl1_id, 22 as tbl2_id, 'fuga2-2' as name, '2020-02-02' as created_at
    union all
    select 2 as tbl1_id, 23 as tbl2_id, 'fuga2-3' as name, '2020-02-02' as created_at
  ),
  tbl3 as (
    select 1 as tbl1_id, 'hogefuga1-1' as name, '2020-02-01' as created_at
    union all
    select 2 as tbl1_id, 'hogefuga2-1' as name, '2020-02-01' as created_at
  ),
  tbl_1_2 as (
    select
      tbl1.tbl1_id,
      tbl2.tbl2_id,
    from tbl1
    inner join tbl2 on tbl2.tbl1_id = tbl1.tbl1_id and tbl2.created_at > tbl1.created_at
    qualify row_number() over(partition by tbl1.tbl1_id order by tbl2.created_at) = 1
  )
select
  tbl1.*,
  tbl2.* except(tbl1_id),
  tbl3.* except(tbl1_id),
from tbl1
inner join tbl_1_2 on tbl_1_2.tbl1_id = tbl1.tbl1_id
inner join tbl2 on tbl2.tbl2_id = tbl_1_2.tbl2_id
inner join tbl3 on tbl3.tbl1_id = tbl1.tbl1_id