AppAnalytics-06.sql

-- Step 3 is to find the session invertval for every user for all days in the period
select 
    steptwo.user_pseudo_id,
    steptwo.event_date,
    DATE_DIFF(date(steptwo.session_start), COALESCE(date(steptwo.last_session_start_ts),date(steptwo.session_start)), DAY) AS session_invertal,
    steptwo.country,
    steptwo.OS
    from (
        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
            
    ) steptwo