How to join two tables to determine date ranges when one table contains (id, start_date) and another contains (id, end_date)

I’m new to SQL, hope you guys don’t find it silly. Working with two tables here, one contains start dates and other contains end dates. Entries do not follow sequence/possibility of duplicates.

**TABLE 1**
id  start_date
1   2019-04-23
1   2019-06-05
1   2019-06-05
1   2019-10-29
1   2019-12-16
2   2019-01-05
3   2020-02-01

**TABLE 2**
id  end_date
1   2019-04-23
1   2019-06-05
1   2019-06-06
1   2019-06-06
1   2019-07-24
1   2019-10-16
2   2020-01-04

**EXPECTED OUTPUT**
id  start_date  end_date
1   2019-04-23  2019-06-05
1   2019-10-29  null
2   2019-01-05  2020-01-04
3   2020-02-01  null

Answer

You can use union all and aggregation with some window functions:

with table1 as (
      select 1 as id, date('2019-04-23') as start_date union all
      select 1,   '2019-06-05' union all
      select 1,   '2019-06-05' union all
      select 1,   '2019-10-29' union all
      select 1,   '2019-12-16' union all
      select 2,   '2019-01-05' union all
      select 3,   '2020-02-01'
     ),
     table2 as (
      SELECT 1 as id, DATE('2019-04-23') as end_date union all
      SELECT 1,   '2019-06-05' union all
      select 1,   '2019-06-06' union all
      select 1,   '2019-06-06' union all
      select 1,   '2019-07-24' union all
      select 1,   '2019-10-16' union all
      select 2,   '2020-01-04'
     ) 
select id, min(start_date), end_date
from (select id, start_date,
         first_value(end_date ignore nulls) over (partition by id order by DATE_DIFF(coalesce(start_date, end_date), CURRENT_DATE, day) RANGE between 1 following and unbounded following) as end_date
      from ((select id, start_date, null as end_date
             from table1
            ) union all
            (select id, null as start_date, end_date
             from table2
            )
           ) se
     )
group by id, end_date
having min(start_date) is not null;