【Bigquery】GA360+BQ：ページ滞在時間の集計

SELECT 
  fullVisitorId,
  hits.page.hostname,
  hits.page.pagePath,
  hits.hitNumber,
  hits.time,
  nextTime,
  CASE
    WHEN hits.isExit IS NOT NULL THEN last_interaction - hit_time
    ELSE next_pageview - hit_time
  END
  AS time_on_page
FROM(
  SELECT
    fullVisitorId, 
    hits.page.hostname,
    hits.page.pagePath,
    hits.hitNumber,
    hits.isExit,
    hits.time/1000 as hits_time,
    LEAD(hits.time/1000, 1) OVER (PARTITION BY fullVisitorId, visitNumber ORDER BY hits.time ASC) as nextTime,
    MAX(IF(hits.isInteraction = TRUE,hits.time / 1000,0)) OVER (PARTITION BY fullVisitorId, visitStartTime) AS last_interaction
  FROM [PROJECTID:DATASETID.ga_sessions_YYYYMMDD]
  WHERE hits.type = "PAGE"
)
### ```