SQL Query get session duration by firebase events-CodePudding

Im need to know the duration of the sessions one by one of my users, to do that i use bigquery, in the next query i try to get the time, but to get you in all the context:

the param ga_session_id propagate for all the event in a session, then I want to rest the timestamp of the session_start (the start of the session) and the last event with this ga_session_id, that for each ga_session_id.

WITH grps AS (
      SELECT event_timestamp, event_name,
        (SELECT value.int_value FROM UNNEST(event_params)
    WHERE key = "ga_session_id") AS sessionid,
             COUNTIF(event_name = 'session_start') OVER (ORDER BY event_timestamp) as grp
      FROM `nodal-descent-XXXXX.analytics_XXXXXX.events_intraday_*`
     ) 
SELECT min(event_timestamp), max(event_timestamp),
      timestamp_diff(timestamp_micros(max(event_timestamp)), 
      timestamp_micros(min(event_timestamp)), second) as se
FROM grps

An example of the data i have:

Anyone can help me to complete the query and do that but by each ga_session_id?

CodePudding user response：

If I understand your question, you are looking to add the session id to your current query. If so try the following:

select
  ep.value.int_value as ga_session_id
  , min(event_timestamp) min_ses
  , max(event_timestamp) max_ses
  , timestamp_diff(timestamp_micros(max(event_timestamp)), timestamp_micros(min(event_timestamp)), second) as se
from bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131,
  UNNEST(event_params) ep
where ep.key='ga_session_id'
group by   ga_session_id
order by ga_session_id