avg query

select 
  sub_region
  --, build_name
  --, case when build_name like "%scale%" then 1 else 0 end as hub_scale
  , os
 , AVG(duration) as duration
 ,  AVG(rails_start_time + firecmd_time + start_session_time) as session_start_time
 ,  AVG(inside_bs_network_time) as inside_bs_network_time
 , AVG(duration - inside_bs_network_time - (firecmd_time + start_session_time))  as user_to_hub_time
 ,  AVG(hubTime) as hubTime 
 , AVG(inside_bs_network_time - totalJarTime - hubTime) as hub_to_terminal_time_new
 ,  AVG(totalJarTime) as inside_terminal_time
 , AVG(nginxToHub) as nginxToHub

 , AVG(numOfReqInJar) as numOfReqInJar
 , count(*) as total_sessions
 from
 (
     select hashed_id
    --  , build_name
     , REGEXP_EXTRACT(build_name, r'disk(.*?)sel') as build_name
     , terminal_properties
     , terminal_region
     , bs_region
     , sub_region
     , selenium_version
     , feature_usage
     , duration*1000 as duration
     , start_time
     , stop_time
     , outside_bs_time
     , inside_bs_network_time
     , rails_start_time
     , firecmd_time
     , start_session_time
     , atsp.os as os
     ,created_day
     , cast(json_extract_scalar(feature_usage, '$.jarPerformance.data.totalJarTime') as int64) as totalJarTime
     , cast(json_extract_scalar(feature_usage, '$.hubProcessing.nginxToHubTime') as int64) as nginxToHub
     , cast(json_extract_scalar(feature_usage, '$.hubProcessing.hubTime') as int64) as hubTime
     , cast(json_extract_scalar(feature_usage, '$.jarPerformance.data.numOfReqInJar') as int64) as numOfReqInJar
     from `browserstack-production.automate.automate_test_sessions_partitioned` as atsp
     join `browserstack-production.common.terminals` as t on atsp.terminal = t.name
      where (DATE(_PARTITIONTIME)  = "2023-02-24" or _PARTITIONTIME is null)
     and duration is not null
    and build_name like "automate latency Analysis- Core Hub Scale"
    -- and build_name like "%single"
     and status = "done"
    --  and json_extract_scalar(feature_usage, '$.jarPerformance.data.totalJarTime') is not null
    --  and json_extract_scalar(feature_usage, '$.jarPerformance.data.numOfReqInJar') is not null
    --  and json_extract_scalar(feature_usage, '$.hubProcessing.nginxToHubTime') is not null
     --limit 1000
 )
--  where numOfReqInJar > 0
where SUBSTR(terminal_region,0,7) =  SUBSTR(bs_region,0,7)
group by 1,2
order by 2,1 
-- order by sub_region, terminal_type,hub_scale, terminal_ip