How to get “session duration” group by “operating system” in Firebase Bigquery SQL?

I try to get the “average session duration” group by “operating system” (device.operating_system) and “date” (event_date).

In the firebase blog, they give us this query to get the average duration session

SELECT SUM(engagement_time) AS total_user_engagement 
FROM (
  SELECT user_pseudo_id, 
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 
      "engagement_time_msec") AS engagement_time
  FROM `FIREBASE_PROJECT` 
)
WHERE engagement_time > 0
GROUP BY user_pseudo_id

This query give me the total user engagement by user ID (each row is a different user):

row|total_user_engagement
---|------------------
1  |989646
2  |225655
3  |125489
4  | 58496
...|......

But I have no idea where I have to add the “operating system” and “event_date” variables to get this information by os and date. I tried differents queries with no result. For example to get this result by operatiing system I tried the following

SELECT SUM(engagement_time) AS total_user_engagement 
FROM (
  SELECT device.operating_system, 
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 
      "engagement_time_msec") AS engagement_time
  FROM `FIREBASE_PROJECT` 
)
WHERE engagement_time > 0
GROUP BY device.operating_system

But it gives me an error message (Error: Unrecognized name: device at [9:10] ). In others queries device.operating_system is recognized.

For example in that one :

SELECT 
  event_date,
  device.operating_system as os_type,
  device.operating_system_version as os_version,
  device.mobile_brand_name as device_brand, 
  device.mobile_model_name as device_model,
  count(distinct user_pseudo_id) as all_users
FROM `FIREBASE Project`
GROUP BY 1,2,3,4,5

What I would like to have as a result is something like this :

row|event_date|OS     |total_user_engagement
---|----------------------------------------
1  |20191212  |ios    |989646
2  |20191212  |android|225655
3  |20191212  |ios    |125489
4  |20191212  |android| 58496
...

Thank you

Answer

The error is probably because you are referencing the variable device in the outer query, while this variable is only visible from the inner query (subquery). I believe the issue will be fixed by changing the last row of the query from GROUP BY device.operating_system to GROUP BY operating_system.

Hopefully this will make clearer what is happening here: the inner query is accessing the table FIREBASE_PROJECT and returning the field operating_system from the nested column device. The outer query accesses the results of the inner query, so it only sees the returned field operating_system, without information about its original context within the nested variable device. That is why trying to reference device at this level will fail.

In the other example you posted this issue does not appear, since there is only a simple query.