您可以尝试使用lag（）
演示

select distinct ID from
(
select *,lag(event) over(partition by id order by dateval) as prevval
from t
)A where prevval='Promoted'

如果你想立即后，那么你会使用 lag() . 如果您想在之后的任何时间使用聚合：

select id
from t
group by id
having max(case when event = 'Promoted' then dateval end) < max(case when event = 'Terminated' then dateval end);

使用 lag() ，代码如下所示：

select id
from (select t.*, lag(event) over (partition by id order by dateval) as prev_event
      from t
     ) t
where prev_event = 'Promoted' and event = 'Terminated';

一个简单的 exists check也可以解决这个简单的需求。
演示

select * from table1 a
where event='Terminated'
and exists(select 1 from table1 b where a.ID = b.ID  and event='Promoted');

输出：

ID  date1       event
1   20191209    Terminated

我们甚至可以比较相关子查询中的事件日期，如演示链接所示。