Osiyuks

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, "{\"Type\": \"Some_type\", \"Identification\": {\"Name\": \"First Last\"}}" raw_json UNION ALL
  SELECT 2, '''{"Type": "Some_type", "Identification": {"Name": "First Last"}}''' UNION ALL
  SELECT 3, '''"{\"Type\": \"


                     null1\"}"
  '''
)

Osiyuks

WITH t AS (
  SELECT
    event_date,
    user_pseudo_id,
    geo.country,
    (event_timestamp - user_first_touch_timestamp)/1000000 AS fotime
  FROM `firebase-public-project.analytics_153293282.events_*`
    WHERE _TABLE_SUFFIX between FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY))
    and FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
    

Osiyuks

WITH a AS (
  # a table with points around the world
  SELECT * FROM UNNEST([ST_GEOGPOINT(-70, -33), ST_GEOGPOINT(-122,37), ST_GEOGPOINT(151,-33)]) my_point
), b AS (
  # any table with cities world locations
  SELECT *, ST_GEOGPOINT(lon,lat) latlon_geo
  FROM `fh-bigquery.geocode.201806_geolite2_latlon_redux` 
)

SELECT my_point, city_name, subdivision_1_name, country_name, continent_name

Osiyuks

SELECT 
  case
  when EXTRACT(DAYOFWEEK FROM date(TIMESTAMP_MICROS(event_timestamp)))=1 then 'SUNDAY'
  when EXTRACT(DAYOFWEEK FROM date(TIMESTAMP_MICROS(event_timestamp)))=2 then 'MONDAY'
  when EXTRACT(DAYOFWEEK FROM date(TIMESTAMP_MICROS(event_timestamp)))=3 then 'TUESDAY'
  when EXTRACT(DAYOFWEEK FROM date(TIMESTAMP_MICROS(event_timestamp)))=4 then 'WEDNESDAY'
  when EXTRACT(DAYOFWEEK FROM date(TIMESTAMP_MICROS(event_timestamp)))=5 then 'THURSDAY'
  when EXTRACT(DAYOFWEEK FROM date(TIMESTAMP_MICROS(event_timestamp)))=6 then 'FRIDAY'
  when EXTRACT(DAYOFWEEK FROM date(TIMESTAMP_MICROS(event_timestamp)))=7 then 'SATURDAY'
  else 'other'end as dayofweek,

Osiyuks

with t1 as (
 SELECT 
  user_pseudo_id, 
  event_name, 
  event_timestamp
 FROM `firebase-public-project.analytics_153293282.events_20181003`
  where event_name = "first_open" 
  or event_name = "use_extra_steps" 
  or event_name = "completed_5_levels"

Osiyuks

SELECT
  COUNTIF(step1=1) AS users_on_step1,
  COUNTIF(step2=1 AND step1=1) AS users_on_step2,
  COUNTIF(step3=1 AND step2=1 AND step1=1) AS users_on_step3,
  COUNTIF(step3=1 AND step2=0 AND step1=1) AS users_on_step4_without_step2
FROM (
  SELECT
    user_pseudo_id,
    IF(COUNTIF(event_name = "first_open")=0,0,1) AS step1,
    IF(COUNTIF(event_name = "use_extra_steps")=0,0,1) AS step2,

Osiyuks

CREATE TEMP FUNCTION MEDIAN(arr ANY TYPE) AS ((
  SELECT
    IF(
      MOD(ARRAY_LENGTH(arr), 2) = 0,
      (arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2) - 1)] + arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2))]) / 2,
      arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2))]
    )
  FROM (SELECT ARRAY_AGG(x ORDER BY x) AS arr FROM UNNEST(arr) AS x)
));

Osiyuks

with subQ_googleanalytic as(  
select parse_date("%Y%m%d",date) as GA_date  
,COUNT(DISTINCT CONCAT(CAST(fullvisitorid AS string),CAST(visitid AS string))) AS no_sessions  
from `******.ga_sessions_*`  
, unnest(hits) as hits  
where _TABLE_SUFFIX between  
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)) AND  
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))  
and totals.visits = 1 --The value is null if there are no interaction events in the session.  
and hits.page.hostname is null  

Osiyuks

SELECT
  @run_time AS time,
  COUNT(*) AS n
FROM
  `firebase-public-project.analytics_153293282.events_*`
WHERE
  _TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", @run_date)
GROUP BY
  1

Osiyuks

select 
--ds, 
cd7 as test,	countif(viewersCVwelcome=1) as step1, countif(click=1) as click, countif(step1=1 and viewersCVwelcome=1) as CVCreated
from
(select 
clientId, regexp_extract(cd.value, r'qvYudVvHR9CijH5BVuUAyQ.\d+.(\d+)') as cd7, FORMAT_DATE("%Y%m%d", DATE(TIMESTAMP_SECONDS(timestamp))) AS ds,
if(countif(eventInfo.eventCategory = 'cvBilderDesktop'
and eventInfo.eventAction = 'View'
and eventInfo.eventLabel = '2')=0,0,1) as viewersCVwelcome,
if(countif((eventInfo.eventCategory = 'clickForm'