seyhunak

# 仅去掉了烦人的提示，足够了。
# 使用说明
# ruby crack.rb "/Applications/Sublime Text 2.app/Contents/MacOS/Sublime Text 2"
# only for build 2210
# coded by hhuai.

#特征码取值
magic_code="\x55\x48\x89\xE5\x53\x48\x83\xEC\x08\x80\x3D\x10\x56\x51\x00\x00\x75\x70\xE8\x09\x7B\xF1\xFF\x48\x89\xC3\x48\x89\xD8\x48\x2B\x05\xC4\x17\x51\x00\x48\x3D\x80\xCB\xA4\x00\x7C\x56\xFF\x05\xBE\x17\x51\x00\xE8\xB7\xE7\x2C\x00\x8B\x0D\xB3\x17\x51\x00\x83\xF9\x02\x0F\x9F\xC2\xA8\x0F\x0F\x94\xC0\x84\xC2\x75\x05\x83\xF9\x08\x7E\x31\xC7\x05\x95\x17\x51\x00\x00\x00\x00\x00\x48\x89\x1D\x86\x17\x51\x00\x31\xFF\x48\x8D\x35\x9D\xC8\x3B\x00\x48\x8D\x15\x6B\xC9"
crack_code="\x55\x48\x89\xE5\x53\x48\x83\xEC\x08\x80\x3D\x10\x56\x51\x00\x00\x74\x70\xE8\x09\x7B\xF1\xFF\x48\x89\xC3\x48\x89\xD8\x48\x2B\x05\xC4\x17\x51\x00\x48\x3D\x80\xCB\xA4\x00\x7C\x56\xFF\x05\xBE\x17\x51\x00\xE8\xB7\xE7\x2C\x00\x8B\x0D\xB3\x17\x51\x00\x83\xF9\x02\x0F\x9F\xC2\xA8\x0F\x0F\x94\xC0\x84\xC2\x75\x05\x83\xF9\x08\x7E\x31\xC7\x05\x95\x17\x51\x00\x00\x00\x00\x00\x48\x89\x1D\x86\x17\x51\x00\x31\

seyhunak

select (case when tile = 50 then 'Median' when tile = 95 then '95%' else '5%' end) as tile
    , app_display_version
    , max(cast( ROUND(duration/1000)/1000 as numeric) ) max_duration_s
    , min(cast( ROUND(duration/1000)/1000 as numeric) ) min_duration_s
    
from (
    select 
         trace_info.duration_us duration
        , ntile(100) over (partition by (app_display_version) order by trace_info.duration_us) tile
        , app_display_version

seyhunak

select (case when tile = 50 then 'Median' when tile = 95 then '95%' else '5%' end) as tile
    , radio_type
    , max(cast( ROUND(duration/1000) as numeric)/1000 ) max_duration_s
    , min(cast( ROUND(duration/1000) as numeric)/1000 ) min_duration_s
    
from (
    select 
         trace_info.duration_us duration
        , ntile(100) over (partition by (radio_type) order by trace_info.duration_us) tile

seyhunak

select 
      (case when tile = 50 then 'Median' when tile = 95 then '95%' else '5%' end) as tile
    , dt
    , max(cast( ROUND(duration/1000) as numeric)/1000 ) max_duration_s
    , min(cast( ROUND(duration/1000) as numeric)/1000 ) min_duration_s
    
from (
    select 
         trace_info.duration_us duration
        , ntile(100) over (partition by (DATE(event_timestamp)) order by trace_info.duration_us) tile

seyhunak

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

seyhunak

#standardSQL
SELECT 
  event_id, issue_id, blame_frame.file, blame_frame.line
FROM 
  `projectId.firebase_crashlytics.package_name_ANDROID`
WHERE 
  FORMAT_TIMESTAMP("%Y%m%d", TIMESTAMP_TRUNC(event_timestamp, DAY)) = '20190509'

seyhunak

SELECT COUNT(distinct p.user_id)
  FROM `friendly--pix.projectId.firebase_predictions.YOUR_PACKAGE_NAME_ANDROID` p CROSS JOIN UNNEST (predictions)
    INNER JOIN  `projectId.analytics_YOUR_TABLE.events_*`` a on p.user_id = a.user_id 
WHERE 
    prediction_name = "churn"   
    AND score > 0.7
    AND TIMESTAMP_TRUNC(p.prediction_time, DAY) = TIMESTAMP('2018-10-04') 
    AND a.geo.country = "France"
    
    

seyhunak

SELECT COUNT(distinct p.user_id)
  FROM `friendly--pix.projectId.firebase_predictions.YOUR_PACKAGE_NAME_ANDROID` p CROSS JOIN UNNEST (predictions)

WHERE 
    prediction_name = "churn"   
    AND score > 0.7
    AND TIMESTAMP_TRUNC(p.prediction_time, DAY) = TIMESTAMP('2018-10-04') 

seyhunak

#standardSQL
####################################################################
# PART 1: Cohort of New Users starting on SEPT 1
####################################################################
WITH 
new_user_cohort AS (  
  SELECT DISTINCT user_pseudo_id as new_user_id
  FROM 
    `projectId.analytics_YOUR_TABLE.events_*`
  WHERE

seyhunak

SELECT
  AVG(trace_info.duration_us / 1000000) AS seconds_on_screen,
  AVG(trace_info.screen_info.slow_frame_ratio) AS slow_frame_ratio,
  AVG(trace_info.screen_info.frozen_frame_ratio) AS frozen_frame_ratio,
  event_name as screen,
  country
FROM `ibam-d6cd2.firebase_performance.iba_mobilbank_IOS`
WHERE _PARTITIONTIME > TIMESTAMP("2019-05-09")
AND event_type = "SCREEN_TRACE"
AND country = "FR"