SQL实现latest与history表变更追踪及合并表生成求助

我有latest和history两张表：history存储所有历史加载的行数据，latest存储最新数据。需要基于load_date生成一张合并表，明确标记变更发生的时间，目前在追踪特定日期的变更及更新时遇到困难，尤其是无法准确记录ID首次出现和最后出现的日期。

示例表 #

latest表 #

id   col1 col2 load_date
1001    a   g   1/3/2024
1003    q   r   1/3/2024

history表 #

id    col1 col2 load_date
1001    a   b   1/1/2024
1002    d   e   1/1/2024
1001    a   g   1/2/2024

期望生成的合并表 #

id     col1 col2 load_date  change
1001    a   b    1/1/2024   new entry
1001    a   g    1/2/2024   col2 changed
1001    a   g    1/3/2024   
1002    d   e    1/1/2024   new entry
1002    d   e    1/2/2024   no show
1003    q   r    1/3/2024   new entry

尝试的SQL代码 #

create table latest(id int, col1 varchar, col2 varchar, load_date date);

insert into latest 
values(1001,'a','g','1/3/2024'),
      (1003,'q','r','1/3/2024');--newly showed up on 1/3

--select * from latest;

create table history(id int, col1 varchar, col2 varchar, load_date date);

insert into history 
values
(1001,'a','b','1/1/2024'),
(1002,'d','e','1/1/2024'),
(1001,'a','g','1/2/2024');--colb changed on 1/2
--(1002,'d','e','1/2/2024')--did not show up
--select * from history;

with combined as
 (
 select *,'latest' as source from latest l
 union all
 select *, 'history' as source from history h
 ),

changes AS (
    SELECT
        ct1.id,
        ct1.col1,
        ct1.col2,
        ct1.load_date,
        CASE
            WHEN ct1.col1 <> ct2.col1 THEN 'col1 changed'
            WHEN ct1.col2 <> ct2.col2 THEN 'col2 changed'
            ELSE NULL
        END AS changes
    FROM
        combined ct1
        LEFT JOIN combined ct2
        ON ct1.id = ct2.id
)

select * from changes

内容的提问来源于stack exchange，提问作者Rick