Skip to content

Instantly share code, notes, and snippets.

@cgi-ace

cgi-ace/AppAnalytics-05.sql Secret

Created June 16, 2021 06:53
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save cgi-ace/2699c522f48a58d0bc3f73b0e54af021 to your computer and use it in GitHub Desktop.
Save cgi-ace/2699c522f48a58d0bc3f73b0e54af021 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
AppAnalytics-05.sql
-- Step 2 is to identify the previous engagement session for every user
select
stepone.user_pseudo_id,
stepone.event_date,
stepone.session_start,
stepone.country,
stepone.OS,
lag(stepone.session_start,1) over (partition by stepone.user_pseudo_id order by stepone.session_start) as last_session_start_ts
from
(SELECT
user_pseudo_id,
event_date,
min(TIMESTAMP_MICROS(event_timestamp)) as session_start,
geo.country as country,
device.operating_system as OS
FROM `firebase-public-project.analytics_153293282.events_*`
where event_name = 'user_engagement'
and _TABLE_SUFFIX between '20180601'and '20181031'
and geo.country = 'Australia'
group by user_pseudo_id,event_date,country,OS
) stepone
group by
stepone.user_pseudo_id,
stepone.event_date,
stepone.session_start,
stepone.country,
stepone.OS
order by user_pseudo_id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment