Time on Page with Windowing Function

Tags:

February 15, 2016
SELECT
  fullVisitorId,
  visitId,
  hits.hitNumber,
  hits.page.pagePath,
  -- get next and previous hit time to be able to work out length of each hit
  hits.time AS hit_time,
  LEAD(hits.time, 1) OVER (PARTITION BY fullVisitorId, visitId ORDER BY hits.time ASC) AS next_hit_time,
  FROM
    [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
  WHERE
    hits.type = "PAGE"