P90

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

 , APPROX_QUANTILES(numOfReqInJar, 100)[offset(90)] 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