title: Postgres: Session duration per event (row)
body: <p>I'm trying to write a query that builds a session duration per each event.
  The database houses events from a webapp, each with a session-id and a timestamp.
  Each row represents one event.
  I thought I could solve this with a recursive query, but every attempt runs for minutes with no return. It's driving me crazy.
  This is what I have so far.</p>
  <pre><code>with recursive session_time as (
      select
          f.data-&gt;'sessionId' as session_id,
          f.ts,
          null::timestamp with time zone as prev_timestamp,
          0 as session_duration
      from arbiter_events as f
      union
      select
          n.data-&gt;'sessionId' as session_id,
          n.ts,
          st.ts as prev_timestamp,
          (EXTRACT(epoch from (n.ts - (
              select
                  st.ts
              from arbiter_events p
              where p.ts &lt; n.ts
              order by p.ts desc
              limit 1
          ))) + st.session_duration)::integer as session_duration
      from arbiter_events as n
      inner join session_time st on st.session_id = n.data-&gt;'sessionId'
  )
  SELECT
      ae.customer,
      ae.username,
      ae.data-&gt;'category' as category,
      ae.data-&gt;'subCategory' as subcategory,
      st.session_id,
      st.session_duration
  from arbiter_events ae
  left join session_time st on ae.data-&gt;'sessionId' = st.session_id;
  </code></pre>