PostgreSQL Calculating a Consecutive session
<p>I have a very large table that contains 4 columns: 1) the status property of a member has changed to:
online, offline, game_lobby, load_screen 2) the status property of a member has changed from: online, offline, game_lobby, and load_screen 3) a member's ID number and 4)the timestamp of when the status property changed).  I want to calculate the average time all members spend online, which would be the difference between the timestamp of when a state changes from online to offline and the timestamp of when a state changes from offline to online:
<a href="http://i.stack.imgur.com/nNgJA.png" rel="nofollow">sample dataset</a> </p>

<p>Using the sample linked above, the average calculated would be (01/03/2016 15:32:05 - 01/02/2016 07:18:32 + 03/14/2016 05:46:41 - 03/14/2016 04:09:04
)/2  </p>

<p>Here's what I wrote, which gave me a few negative averages calculated for certain members, which can't be right:</p>

<pre><code>with sessions as
( select 
    date_trunc('week', sc.occurred_at) as week,
    sc.occurred_at,
    sc.id,
    timestampdiff(second,lag(sc.occurred_at) over (order by sc.id asc, sc.occurred_at),
    sc.occurred_at)/3600 as session
  from state_changes sc
  where
    ((from_state = 'offline' and to_state = 'online') or
     (from_state = 'offline' and to_state = 'online'))
    and occurred_at at time zone 'America/New_york' &gt; '2016-01-01'
)
select week, avg(session), id
from sessions
group by 1,3;
</code></pre>

<p>I can roll-up the averages into a single value instead of by member, but what I wrote is clearly wrong since a small number of the averages are returning negative. Does anyone have any suggestions?</p>