Skip to content

Instantly share code, notes, and snippets.

@seyhunak

seyhunak/login_ios_country.sql

Forked from mshakhomirov/login_ios_country.sql
Created January 20, 2022 11:47
Show Gist options
  • Save seyhunak/67ee54dc65f29b1df04fed7c7716a67b to your computer and use it in GitHub Desktop.
Save seyhunak/67ee54dc65f29b1df04fed7c7716a67b to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @mshakhomirov mshakhomirov revised this gist Jul 16, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion login_ios_country.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -7,7 +7,7 @@ select (case when tile = 50 then 'Median' when tile = 95 then '95%' else '5%' en
    from (
    select
    trace_info.duration_us duration
    , count(trace_info.duration_us ) OVER(partition by (country)) logonCount
    , 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
  2. @mshakhomirov mshakhomirov created this gist Jul 16, 2020.
    29 changes: 29 additions & 0 deletions login_ios_country.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,29 @@
    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
    , 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