By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table t as
select 'a' as name, '2020-01-01'::date as event_time, 'run' as event_type union all
select 'a' as name, '2020-01-03'::date as event_time, 'run' as event_type union all
select 'a' as name, '2020-01-05'::date as event_time, 'end' as event_type union all
select 'a' as name, '2020-01-18'::date as event_time, 'run' as event_type union all
select 'a' as name, '2020-01-29'::date as event_time, 'end' as event_type union all
select 'b' as name, '2020-02-12'::date as event_time, 'run' as event_type union all
select 'b' as name, '2020-03-01'::date as event_time, 'end' as event_type
7 rows affected
select t.*,
(1 + sum( (event_type = 'end')::int ) over () -
sum( (event_type = 'end')::int ) over (order by event_time desc)
) as group_id
from t
order by event_time
| name | event_time | event_type | group_id |
|---|---|---|---|
| a | 2020-01-01 | run | 1 |
| a | 2020-01-03 | run | 1 |
| a | 2020-01-05 | end | 1 |
| a | 2020-01-18 | run | 2 |
| a | 2020-01-29 | end | 2 |
| b | 2020-02-12 | run | 3 |
| b | 2020-03-01 | end | 3 |