drop table if exists program_runs;

create temporary table program_runs (
    id serial,
    time_stamp timestamptz,
    action text
);

insert into program_runs (time_stamp, action) values
    ('2012-01-01 10:00:00 PST', 'started'), ('2012-01-01 10:10:00 PST', 'stopped early'),
    ('2012-01-01 10:20:00 PST', 'started'), ('2012-01-01 10:30:00 PST', 'stopped early'),
    ('2012-01-01 10:40:00 PST', 'started'), ('2012-01-01 10:47:00 PST', 'completed'),
    ('2012-01-01 10:50:00 PST', 'started'), ('2012-01-01 11:00:00 PST', 'stopped early'),
    ('2012-01-01 11:10:00 PST', 'started'), ('2012-01-01 11:13:00 PST', 'completed'),
    ('2012-01-01 11:20:00 PST', 'started'), ('2012-01-01 11:30:00 PST', 'stopped early'),
    ('2012-01-01 11:40:00 PST', 'started'), ('2012-01-01 11:50:00 PST', 'stopped early'),
    ('2012-01-01 12:00:00 PST', 'started'), ('2012-01-01 12:10:00 PST', 'stopped early'),
    ('2012-01-01 12:20:00 PST', 'started'), ('2012-01-01 12:29:00 PST', 'completed');

with
    windowed as (
        select
            time_stamp as this_time_stamp,
            action as this_action,
            lag(time_stamp) over (order by time_stamp) as prev_time_stamp,
            lag(action) over (order by time_stamp) as prev_action,
            lead(time_stamp) over (order by id) as next_time_stamp,
            lead(action) over (order by id) as next_action
        from program_runs
    ),
    detail as (
        select
            this_time_stamp as starting_time_stamp,
            next_time_stamp as ending_time_stamp,
            next_time_stamp - this_time_stamp as time_elapsed,
            next_action as closing_action
        from windowed
        where this_action = 'started'
    ),
    bookends as (
        select this_bookend, that_bookend, row_number() over () as bookend_rownum
        from (
            select
                this_action,
                this_time_stamp as this_bookend,
                lead(this_time_stamp) over (order by this_time_stamp) as that_bookend
            from windowed
            where prev_action is null
            or prev_action = 'completed'
            or next_action is null
            or next_action = 'started' and this_action = 'completed'
        ) q
        where this_action = 'started'
    )
select
    bookends.this_bookend,
    bookends.that_bookend,
    bookends.that_bookend - bookends.this_bookend as clock_time,
    sum(detail.time_elapsed) as proc_time
from detail join bookends
    on detail.starting_time_stamp between bookends.this_bookend and bookends.that_bookend
group by bookends.bookend_rownum, bookends.this_bookend, bookends.that_bookend;
