Group consecutive events into user sessions.

WITH previous AS(
  SELECT
    *,
    LAG(event_at) OVER(PARTITION BY member_id ORDER BY event_at,id) AS previous_at
  FROM social_events
), marked AS(
  SELECT
    *,
    CASE
      WHEN previous_at IS NULL
        OR unixepoch(event_at)-unixepoch(previous_at)>=1800
        THEN 1
      ELSE 0
    END AS new_session
  FROM previous
), numbered AS(
  SELECT
    *,
    SUM(new_session) OVER(PARTITION BY member_id ORDER BY event_at,id ROWS UNBOUNDED PRECEDING) AS session_number
  FROM marked
)
SELECT member_id,MAX(session_number) AS session_count
FROM numbered
GROUP BY member_id;