Skip to content

Instantly share code, notes, and snippets.

Avatar
🏆
Winning The Life

Seyhun Akyürek seyhunak

🏆
Winning The Life
View GitHub Profile
View login_ios_version.sql
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
View login_ios_radio.sql
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
View login_ios.sql
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
View login_ios_country.sql
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
seyhunak / get_current_weather.py
Created Sep 15, 2021
Write a Python program to get current weather in Azerbaijan
View get_current_weather.py
import requests
import json
def get_current_weather(city):
url = 'http://api.openweathermap.org/data/2.5/weather'
params = {
'q': city,
'appid': '11c0d3dc6093f7442898ee49d2430d20',
'units': 'metric'
@seyhunak
seyhunak / crashes.sql
Created Aug 31, 2021 — forked from sbrissenden/crashes.sql
[BigQuery + Firebase Crashlytics] Crashes from May 9th
View crashes.sql
#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
seyhunak / predictions_churn_french_users.sql
Created Aug 31, 2021 — forked from sbrissenden/predictions_churn_french_users.sql
[BigQuery + Firebase Predictions] Number of Users Predicted to Churn - French Users
View predictions_churn_french_users.sql
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
seyhunak / predictions_churn.sql
Created Aug 31, 2021 — forked from sbrissenden/predictions_churn.sql
[BigQuery + Firebase Predictions] Number of Users Predicted to Churn
View predictions_churn.sql
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
seyhunak / retention.sql
Created Aug 31, 2021 — forked from sbrissenden/retention.sql
[BigQuery + Google Analytics for Firebase] Daily N-Day User Retention of September 1 Cohort
View retention.sql
#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
seyhunak / firebase_performance_slow_screens.sql
Last active Aug 31, 2021 — forked from sbrissenden/firebase_performance_slow_screens.sql
[BigQuery + Firebase Performance Monitoring] Find app screens with frozen frames impacting French users
View firebase_performance_slow_screens.sql
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"