您可以使用以下方法轻松获取电子邮件的时间戳：

select activity_id, customer , activity, ts,
       (case when activity = 'completed_order' and
                  (min(ts) filter (where activity = 'email') over (partition by customer order by ts desc) <
                   min(ts) filter (where activity = 'completed_order') over (partition by customer order by ts desc)
                  )
             then min(ts) filter (where activity = 'email') over (partition by customer order by ts desc)
        end) as First_in_between    
from activity_stream
where customer = 'Lehmanns Marktstand'
order by ts;

然后可以连接回表或使用另一级别的窗口函数来获取相应的 activity_id 时间戳。
实际上，我想我更喜欢另一种方法，那就是只计算已完成订单的数量，然后取最小的ts：

select a.*,
       min(ts) filter (where activity = 'email') over (partition by grp) as email_ts
from (select a.*,
             count(*) filter (where activity = 'completed_order') as grp
      from activity_stream a
      where customer = 'Lehmanns Marktstand'
     ) a;

这还应允许您使用twist来获取活动id，而无需附加子查询：

select a.*,
       (array_agg(activity_id order by ts) filter (where activity = 'email') over (partition by grp))[1] as email_activity_id
from (select a.*,
             count(*) filter (where activity = 'completed_order') as grp
      from activity_stream a
      where customer = 'Lehmanns Marktstand'
     ) a