Group engagement time

engagement_time.sql

SELECT
    case
        when engagement.minutes > 20 then '>20 min'
        when engagement.minutes > 10 then '10-20 min'
        when engagement.minutes > 5 then '5-10 min'
        when engagement.minutes > 2 then '2-5 min'
        else '<2 min'
    end as segment,
    count(*) as users
FROM (
    SELECT distinct(user_pseudo_id) as user_pseudo_id FROM `<project>.analytics_205676331.events_*` WHERE event_name = 'view_item'
) as viewers
LEFT JOIN (
    SELECT 
        user_pseudo_id, 
        sum(params.value.int_value) / 60000 as minutes
    FROM `<project>.analytics_205676331.events_*`,
    UNNEST(event_params) AS params
    WHERE params.key='engagement_time_msec'
    group by user_pseudo_id 
) as engagement on engagement.user_pseudo_id = viewers.user_pseudo_id
group by segment