Osiyuks

SELECT  
event_timestamp,
user_pseudo_id,

  -- Какой firebase_screen_class какому экрану соответствует узнайте у своего разработчика
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'firebase_screen_class' ) AS screen

FROM `<project_id>.<dataset>.events_*`
WHERE _TABLE_SUFFIX between "20200806" and "20200816"
and event_name = 'screen_view'

Osiyuks

SELECT event_name, count(*) as n 
FROM `gemini-ios.analytics_163264845.events_*` 
WHERE _TABLE_SUFFIX between "20200801" and "20200805"
group by 1

SELECT distinct cid  
FROM `analytics-176911.datahub.actions` 
WHERE DATE(ngtime) between "2020-08-01" and "2020-08-05"
and an='CleanMyMac X' 

Osiyuks

SELECT * FROM UNNEST(ARRAY<int64>[1, 2, 3, 4, 5])

EXCEPT DISTINCT -- INTERSECT DISTINCT

SELECT * FROM UNNEST(ARRAY<int64>[3, 4, 5, 6, 7])

Osiyuks

with t as (

SELECT  
user_pseudo_id, event_date,
CASE userProperty.value.string_value
            WHEN "0" THEN "Control group"
				WHEN "1" THEN "Variant A"
          END AS test
FROM `<project_id>.<dataset>.events_*`,  -- change your project_id and dataset
            UNNEST(user_properties) AS userProperty

Osiyuks

WITH FIRST_ACTION AS (

SELECT
        TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), DAY) as DATE,
        HLL_COUNT.INIT(user_id) as USER_ID_SET
    FROM `<dataset>.<project_id>.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20200201' AND '20200221' AND event_name = 'session_start'
    GROUP BY 1
    
), RETURNING_ACTION AS (

Osiyuks

-- Source: https://t.me/BigQuery
-- Сalculation example bracket-dependent return retention (https://applift.com/blog/user-retention)
-- weekly (1 week = day2-8, 2 week = day9-15 ...)

WITH download AS (

SELECT 
user_pseudo_id, app_info.version,
EXTRACT (DATE FROM (SELECT TIMESTAMP_MICROS(event_timestamp))) AS day_0 
FROM `your_project.your_table.events_*` 

Osiyuks

case
   when net.reg_domain(lower(email)) is null then false
   when regexp_contains(lower(email), "^[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])$") then true
   else false
end

Osiyuks

SELECT 
  HLL_COUNT.MERGE(users_hll) Users
FROM 
 (SELECT
    trafficSource.source, 
    HLL_COUNT.INIT(fullVisitorId, 14) as users_hll
  FROM `your_ga_table`
    group by 1)

Osiyuks

with t as (

  select 1 id, 'name=Alex; age=30; project=project1; company = MP' Data 
  union all
  select 2, 'name=Vin; age=31; project=project3; company = AC'
  union all
  select 3, 'name=Sara; age=32; company = FF; hobby= cricket'
  
), g as (

Osiyuks

SELECT fhoffa.x.parse_number('one hundred fifty seven')
  , fhoffa.x.parse_number('three point 5')
  , fhoffa.x.parse_number('2 hundred')
  , fhoffa.x.parse_number('minus 8')
  , fhoffa.x.parse_number('5 million 3 hundred 25 point zero 1')
_____________________________
  
157, 3.5, 200, -8, 5000325.01