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 container_edits as
select 'XYZ' as container, 5 as units, 'Start' as status, '2018-01-01 00:00:15' as move_time union all
select 'XYZ' as container, 2 as units, 'Add' as status, '2018-01-01 00:01:10' as move_time union all
select 'XYZ' as container, 3 as units, 'Add' as status, '2018-01-01 00:02:00' as move_time union all
select 'XYZ' as container, null as units, 'Complete' as status, '2018-01-01 00:03:00' as move_time union all
select 'XYZ' as container, 5 as units, 'Start' as status, '2018-01-01 00:04:15' as move_time union all
select 'XYZ' as container, 3 as units, 'Add' as status, '2018-01-01 00:05:10' as move_time union all
select 'XYZ' as container, 4 as units, 'Add' as status, '2018-01-01 00:06:00' as move_time union all
select 'XYZ' as container, 5 as units, 'Add' as status, '2018-01-01 00:07:10' as move_time union all
select 'XYZ' as container, 6 as units, 'Add' as status, '2018-01-01 00:08:00' as move_time union all
select 'XYZ' as container, null as units, 'Complete' as status, '2018-01-01 00:09:00' as move_time
10 rows affected
select container, loop_num, sum(units),
min(move_time), max(move_time)
from (select ce.*,
sum(case when status = 'Start' then 1 else 0 end) over (partition by container order by move_time) as loop_num
from container_edits ce
) ce
group by container, loop_num
| container | loop_num | sum | min | max |
|---|---|---|---|---|
| XYZ | 1 | 10 | 2018-01-01 00:00:15 | 2018-01-01 00:03:00 |
| XYZ | 2 | 23 | 2018-01-01 00:04:15 | 2018-01-01 00:09:00 |