第一步：

• 基于两个结果（即基于ID和结果值的批准和重新批准）创建等级。
• 在rank_ = 1上筛选，这将提取两个结果值的最近值（如果存在）
• 为两个结果值提取每个ID的最新日期后，创建一个计数以检查一个ID是否具有多行
• 创建一个布尔值以过滤掉不相关的行

with main as (
select 
*, row_number (over partition by id, result order by date desc) as rank_
from table
),
totals as (
select *, count(*) over(partition by id) as total_results
 from main where rank_ = 1
),
conditions as (
select 
case 
when total_results = 1 then true
when total_results > 1 and lower(result) = 'approved' then true
else false end as is_relevant_row
from totals
)
select * from conditions where is_relevant_row