Source

WhatHappened / share / SuperHostile14-deaths.sql

Full commit
\set time_group_secs (60*60)
\set life_len_group_secs (5*60)

with
    time_segments as (
        select
            player_user_name,
            to_char(
                timestamp with time zone 'epoch' + interval '1 second'
                * floor(extract('epoch' from time_stamp_minute) / :time_group_secs)
                * :time_group_secs,
                'YYYY-MM-DD HH24:MI'
            ) as time_segment,
            count(*) as minutes,
            sum(deaths) as deaths
        from (
            select player_user_name, date_trunc('minute', time_stamp) as time_stamp_minute,
                sum(case when log_entry_type_name = 'Player died' then 1 else 0 end) as deaths
            from log_entry_list where server_cwd_basename = 'SuperHostile14' and player_user_name is not null
            group by 1, 2
        ) q1 group by 1, 2
    )
select
    player_user_name,
    time_segment,
    deaths,
    minutes,
    round(deaths/minutes, 1) as "d/min",
    round(minutes/(deaths+1), 1)::real as "mins/life"
from (
    select player_user_name, time_segment, minutes, deaths from time_segments
    union all
    select player_user_name, 'total', sum(minutes), sum(deaths) from time_segments group by player_user_name
) q2 order by 1, 2;

drop table if exists minutes_per_life;

create temporary table minutes_per_life as
    with
        actions as (
            select
                player_user_name,
                time_stamp,
                log_entry_type_name,
                case
                    when lag(log_entry_type_name) over (partition by player_user_name order by time_stamp) is null then 'start'
                    when lag(log_entry_type_name) over (partition by player_user_name order by time_stamp) = 'Player died' then 'start'
                    when log_entry_type_name = 'Player died' then 'stop'
                    when lead(log_entry_type_name) over (partition by player_user_name order by time_stamp) is null then 'stop'
                end as life_boundary
            from log_entry_list where server_cwd_basename = 'SuperHostile14' and player_user_name is not null
        ),
        lives as (
            select row_number() over () as life_id, q.*
            from (
                select
                    player_user_name,
                    life_boundary,
                    time_stamp as time_stamp1,
                    log_entry_type_name as log_entry_type_name1,
                    lead(time_stamp) over (partition by player_user_name order by time_stamp) as time_stamp2,
                    lead(log_entry_type_name) over (partition by player_user_name order by time_stamp) as log_entry_type_name2
                from actions
                where actions.life_boundary is not null
            ) q
            where life_boundary = 'start'
        )
        select life_id,
            player_user_name,
            start_time,
            round(
                case
                    when clock_minutes < count(*) then clock_minutes
                    else count(*)::double precision
                end::numeric,
                1
            )::real as life_len_minutes
        from (
            select
                life_id,
                lives.player_user_name,
                time_stamp1 as start_time,
                extract(epoch from time_stamp2 - time_stamp1) / 60 as clock_minutes,
                date_trunc('minute', actions.time_stamp) as active_minute
            from lives join actions on actions.time_stamp between lives.time_stamp1 and lives.time_stamp2
            group by 1, 2, 3, 4, 5
        ) q
        group by life_id, player_user_name, start_time, clock_minutes order by 1;

select * from minutes_per_life;

with 
    life_len_groups as (
        select
            player_user_name,
            life_len_minutes,
            interval '1 second' * life_len_minutes * 60 as life_len,
            :life_len_group_secs * round((life_len_minutes*60)/:life_len_group_secs) / 60 as life_len_group
        from minutes_per_life order by 1
    )
select player_user_name, '   total' as life_len, count(*), min(life_len_minutes), round(avg(life_len_minutes)::numeric, 1)::real as avg, max(life_len_minutes)
from life_len_groups
group by player_user_name
union all
select player_user_name, to_char(life_len_group, '9999999'), count(*), min(life_len_minutes), round(avg(life_len_minutes)::numeric, 1)::real, max(life_len_minutes)
from life_len_groups
group by player_user_name, life_len_group
order by 1, 2