Find sum of engagement_time_msec for users who have done an event named “yt_event” in BigQuery

My table looks like this:

There’s one more column named “user_pseudo_id” which is unique id for users. I want to take sum of event_params.key = ‘engagement_time_msec’ for user_pseudo_id who have done event_name = ‘yt_event’.

Also, event_params.key = ‘engagement_time_msec’ is only present in two events only, i.e. event_name = ‘user_engagement’ and ‘screen_view’.

I have tried subqueries like this:

SELECT
user_pseudo_id,
(
select
sum(value.int_value/60000)
from unnest(event_params)
where
key = 'engagement_time_msec'
and
user_pseudo_id = 'yt_users') as eng_time_min
FROM
  `Xyz.events_20201030`
where
event_name = 'yt_event'

But I am not able to get it.

Please help me. I will be highly obliged.

Thanks.

Answer

Below is for BigQuery Standard SQL

#standardSQL
select user_pseudo_id,
  sum(( select value.int_value/60000
    from t.event_params
    where key = 'engagement_time_msec'
  )) as eng_time_min
from `Xyz.events_20201030` t
where user_pseudo_id in (
  select distinct user_pseudo_id
  from `Xyz.events_20201030`
  where event_name = 'yt_event'
)
group by user_pseudo_id