Skip to content

Instantly share code, notes, and snippets.

@mshakhomirov
Last active January 20, 2022 11:47
Show Gist options
  • Save mshakhomirov/7636280943ca088621d13ce1370aea38 to your computer and use it in GitHub Desktop.
Save mshakhomirov/7636280943ca088621d13ce1370aea38 to your computer and use it in GitHub Desktop.
select (case when tile = 50 then 'Median' when tile = 95 then '95%' else '5%' end) as tile
, country
, max(cast( ROUND(duration/1000)/1000 as numeric) ) max_duration_s
, min(cast( ROUND(duration/1000)/1000 as numeric) ) min_duration_s
, logonCount
from (
select
trace_info.duration_us duration
, count(trace_info.duration_us ) OVER(partition by (country)) logonCount -- you might want to use this as a sort later
, ntile(100) over (partition by (country) order by trace_info.duration_us) tile
, country
-- app_display_version, country, radio_type
FROM `your-project-client.firebase_performance.your-project-client_IOS` d
WHERE
DATE(_PARTITIONTIME) >= PARSE_DATE('%Y%m%d', @DS_START_DATE)
AND DATE(_PARTITIONTIME) <= PARSE_DATE('%Y%m%d', @DS_END_DATE)
AND
date(event_timestamp) >= PARSE_DATE('%Y%m%d', @DS_START_DATE)
AND
date(event_timestamp) <= PARSE_DATE('%Y%m%d', @DS_END_DATE)
AND event_type = "DURATION_TRACE"
AND event_name = 'Logon'
) x
WHERE tile in (5, 50, 95)
group by country, tile, logonCount
order by country
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment