query.sql

-- The view
CREATE VIEW session(
    id,
    visitor_id,
    start_utc_time,
    next_start_utc_time
  ) AS
    SELECT
      pi_with_inactivity.visitor_id || '-' || row_number() OVER(
        PARTITION BY pi_with_inactivity.visitor_id
        ORDER BY
          pi_with_inactivity.utc_time
      ) AS session_id,
      pi_with_inactivity.visitor_id,
      pi_with_inactivity.utc_time AS session_start_at,
      lead(utc_time) OVER(
        PARTITION BY pi_with_inactivity.visitor_id
        ORDER BY
          pi_with_inactivity.utc_time
      ) AS next_session_start_at
    FROM (
        SELECT
          utc_time,
          visitor_id,       
          LAG(utc_time) OVER (
            PARTITION BY visitor_id
            ORDER BY
              utc_time
          ) - utc_time AS inactive_seconds
        FROM page_view
      ) AS pi_with_inactivity
    WHERE
      ABS(pi_with_inactivity.inactive_seconds) > 30 * 60
      OR pi_with_inactivity.inactive_seconds IS NULL

-- The query
SELECT
  count(id) as sessions
FROM
  session
WHERE
  date(start_utc_time, 'unixepoch', 'localtime') <= ? AND
  date(start_utc_time, 'unixepoch', 'localtime') >= ?
;