Days Since Last Session with Window Functions

Tags:

June 26, 2016 | Bounteous x Accolite
SELECT
  fullvisitorid,
  visitId,
  DATEDIFF(SEC_TO_TIMESTAMP(visitStartTime), SEC_TO_TIMESTAMP(prevVisitStartTime)) AS daysSinceLastSession,
  FLOOR((visitStartTime - prevVisitStartTime)/60) as minutesSinceLastSession
FROM (
  SELECT
    fullvisitorid,
    visitId,
    visitStartTime,
    LAG(visitStartTime) OVER (PARTITION BY fullvisitorid ORDER BY visitStartTime ASC) AS prevVisitStartTime
  FROM
    [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910])