AppAnalytics-07.sql

-- Step 4 is to find the total unique users for a given day and Sum of session interval from step 3. Now the sum_session_interval/user_count will give you the avg session interval
-- for a given day. You can then pick the days you want for reporting period
select 
count(distinct Stepthree.user_pseudo_id) as user_count,
sum(Stepthree.session_invertal) as sum_session_invertal_in_days,
Stepthree.event_date,
Stepthree.country,
Stepthree.OS
from
(
    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 
) Stepthree
where Stepthree.session_invertal >0
group by 
Stepthree.event_date,
Stepthree.country,
Stepthree.OS