Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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