Slide 18
Slide 18 text
Find the average duration spent transitioning from page A to page B?
SQL>
SELECT
COUNT(visitId) AS count_visitors,
AVG(hitTime_lead - hitTime) / 1000 AS average_duration_seconds
FROM (
SELECT
visitId, hitTime, path,
LEAD(path, 1) OVER (PARTITION BY visitId ORDER BY hitTime) AS path_lead,
LEAD(hitTime, 1) OVER (PARTITION BY visitId ORDER BY hitTime) AS hitTime_lead
FROM (
SELECT visitId, hits.time AS hitTime, hits.page.pagePath AS path
FROM [bigquerytestdefault:demo.analytics]
OMIT RECORD IF
EVERY(hits.page.pagePath CONTAINS 'publicdata:samples.github_nested')
OR EVERY(hits.page.pagePath CONTAINS 'publicdata:samples.github_timeline') ) )
WHERE
path CONTAINS 'publicdata:samples.github_nested'
AND path_lead CONTAINS 'publicdata:samples.github_timeline'
SQL Limits