BigQuery requests

[BigQuery] Formating DATE _ Standard SQL

You can use TIMESTAMP or DATE or DATETIME and use corresponding FORMAT_* functions

##DAY OF Week
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#supported-format-elements-for-date
%u	The weekday (Monday as the first day of the week) as a decimal number (1-7).

### Option 1
SELECT 
  FORMAT_DATE("%u", CURRENT_DATE()) as currentDayOfWeek;
### Option 2 
SELECT
  FORMAT_DATETIME("%u", DATETIME(CURRENT_DATE(), TIME "15:30:00")) AS currentDayOfWeek;
  
 ##MONTH OF YEAR
 %m	The month as a decimal number (01-12).
 
 ### Option 1
 SELECT FORMAT_DATE("%m", CURRENT_DATE()) as monthInYear;
 ### Option 2
 SELECT
  FORMAT_DATETIME("%m", DATETIME(CURRENT_DATE(), TIME "15:30:00")) AS monthOfYear;

[BigQuery] Formating TIMESTAMP _ Standard SQL

You can use TIMESTAMP or DATE or DATETIME and use corresponding FORMAT_* functions

## TIMESTAMP
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#timestamp-functions

Formating today as 20161212 (using TIMESTAMP)
FORMAT_TIMESTAMP("%Y%m%d", CURRENT_TIMESTAMP()) as today

Setting CURRENT_TIMESTAMP as extra column in Query
SELECT COUNT(*) as counter,
CURRENT_TIMESTAMP() as yes
FROM `namespace_ANDROID.app_events_*`
WHERE _TABLE_SUFFIX IN ("20161201");

## select today & 2 days ago
SELECT
FORMAT_TIMESTAMP("%Y%m%d", CURRENT_TIMESTAMP()) as today,
FORMAT_TIMESTAMP("%Y%m%d",  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24*2 HOUR)) AS twoDaysAgo;

## substracting TIMESTAMP
SELECT
  TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
  TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS earlier;
  
  

[BigQuery] get Best Counter _ Standard SQL

Be carefull
- event_dim.timestamp_micros seems not to always be filled
- event_dim.date seems to be available only since 20161111

#Option 1 (less readable, but quicker)
4.6s elapsed, 3.75 MB processed

SELECT
  FORMAT_TIMESTAMP("%Y%m%d",TIMESTAMP_MICROS(h.timestamp_micros )) as date, -- this is not always filled
  h.name as event,
  COUNT(*) as total,
  max(COUNT(*)) OVER(ORDER BY COUNT(*) DESC) as best
FROM
  `namespace_ANDROID.app_events_*`, UNNEST(event_dim) as h
WHERE
  h.name='in_app_purchase'
AND
  _TABLE_SUFFIX
BETWEEN
  '20161201'  -- first date in time
AND
  FORMAT_DATE("%Y%m%d", CURRENT_DATE())
GROUP BY 1,2
ORDER BY 1 DESC LIMIT 1 -- will get yesterday count and best count in one row

#Option 2 (more readable, take more time)
(5.1 sec, 3,75MB)
SELECT
  date,
  event,
  total,
  max(total) OVER(ORDER BY total DESC) as best
FROM (
  SELECT FORMAT_TIMESTAMP("%Y%m%d",TIMESTAMP_MICROS(h.timestamp_micros )) as date,
  h.name as event,
  COUNT(*) as total
  FROM `namespace_ANDROID.app_events_*`, UNNEST(event_dim) as h
WHERE
  h.name='in_app_purchase'
AND
  _TABLE_SUFFIX
BETWEEN
  '20161201' -- first date in time
AND
  FORMAT_DATE("%Y%m%d", CURRENT_DATE())
GROUP BY 1,2)
ORDER BY 1 DESC LIMIT 1 -- will get yesterday count and best count in one row

## option 3 (using event_dim.date instead of event_dime.timestamp_micros), available only since 20161111
SELECT
  date,
  event,
  total,
  max(total) OVER(ORDER BY total DESC) as best
FROM (
  h.date as date,
  h.name as event,
  COUNT(*) as total
  FROM `namespace_ANDROID.app_events_*`, UNNEST(event_dim) as h
WHERE
  h.name='in_app_purchase'
AND
  _TABLE_SUFFIX
BETWEEN
  '20161201' -- first date in time
AND
  FORMAT_DATE("%Y%m%d", CURRENT_DATE())
GROUP BY 1,2)
ORDER BY 1 DESC LIMIT 1 -- will get yesterday count and best count in one row

[BigQuery] Last Week Range _ Standard SQL

##Last Week range (find the previous monday to previous sunday) -> This will help to get the not rounding Weekly events

Be carefull, we cast FORMAT_DATE to INT64 (as it returns STRING)
SELECT
FORMAT_DATE("%Y%m%d", CURRENT_DATE()) as today,
FORMAT_DATE("%u", CURRENT_DATE()) as currentDayOfWeek,
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) DAY)) AS previousSunday,
FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) + 6 DAY)) AS previousMonday;

To use this in a query, one can extract these fields with the WITH command and reuse them in subqueries
WITH dateQuery AS (
  SELECT FORMAT_DATE("%Y%m%d", CURRENT_DATE()) as today,
  FORMAT_DATE("%u", CURRENT_DATE()) as currentDayOfWeek,
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) DAY)) AS previousSunday,
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) + 6 DAY)) AS previousMonday
)

SELECT
  today, currentDayOfWeek, previousSunday, previousMonday
FROM dateQuery;

[BigQuery] Range of Date _ Standard SQL

#Range of dates (using BETWEEN)
SELECT COUNT(*) as counter
FROM `namespace_ANDROID.app_events_*`
WHERE _TABLE_SUFFIX BETWEEN '20161201' AND '20161202'

#Seperated dates (using IN)
SELECT COUNT(*) as counter
FROM `namespace_ANDROID.app_events_*`
WHERE _TABLE_SUFFIX IN ("20160901", "20161202");

#Range of dates (using DATE_ADD)
SELECT COUNT(*) as counter,
FORMAT_DATE("%Y%m%d", CURRENT_DATE()) as today,
FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) AS oneDayAgo
FROM `namespace_ANDROID.app_events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) AND FORMAT_DATE("%Y%m%d", CURRENT_DATE())

#Range of dates (using DATE_SUB instead of negative DATE_ADD)
SELECT COUNT(*) as counter,
FORMAT_DATE("%Y%m%d", CURRENT_DATE()) as today,
FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) AS oneDayAgo
FROM `namespace_ANDROID.app_events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AND FORMAT_DATE("%Y%m%d", CURRENT_DATE())

average event filter by user property

http://stackoverflow.com/questions/40125614/bigquery-firebase-querying-events-order-by-user-property

SELECT
  user_properties.value.value.string_value as lang,
  AVG((SELECT SUM(value.int_value)
       FROM UNNEST(event_dim),
         UNNEST(params)
       WHERE key = "coins_awarded")) as avg_coins 
FROM 
  `firebase-analytics-sample-data.ios_dataset.app_events_20160607` t,
    UNNEST(user_dim.user_properties) user_properties
WHERE 
  user_properties.key = "language"
GROUP BY 1;


### legacy SQL
SELECT
  user_dim.user_properties.value.value.string_value as lang,
  AVG(event_dim.params.value.int_value) as avg_coins 
FROM 
  FLATTEN([firebase-analytics-sample-data:ios_dataset.app_events_20160607], user_dim.user_properties)
WHERE 
  user_dim.user_properties.key = "language"
  AND event_dim.params.key = "coins_awarded"
GROUP BY 1

flatten nested data into 1 row each

http://stackoverflow.com/questions/38860534/flatten-firebase-exports-to-bigquery-into-tables-where-1-row-1-event-nested-d

SELECT 
  user_dim.app_info.app_instance_id,
  timestamp_micros,
  (SELECT value.int_value FROM UNNEST(dim.params) WHERE key = "level") AS level,
  (SELECT value.int_value FROM UNNEST(dim.params) WHERE key = "coins_awarded") AS coins,
  (SELECT value.int_value FROM UNNEST(dim.params) WHERE key = "powerups") AS powerups
FROM `firebase-analytics-sample-data.ios_dataset.app_events_20160607`, UNNEST(event_dim) AS dim

Get SUM of counts for an event

public static void trackItemsSelectedCount(Context context, int count) {
    Bundle bundle = new Bundle();
    bundle.putInt("count", count);
    getAnalytics(context).logEvent("items_selected", bundle);
}


SELECT 
    SUM((SELECT params.value.int_value FROM x.params WHERE params.key = 'count')) AS count 
FROM package_ANDROID.app_events_20170123, UNNEST(event_dim) AS x 
WHERE x.name = 'items_selected'

Queryies from Youtube Firebase Cast

    8:15
Select event_param.value.double_value
    FROM `namespace.table.app_events_intraday_20161111`
    UNEST(event_dim) as event,
        UNEST(event.params) as event_param
            WHERE event.name = 'adjust_slider' AND event_param.key ='newValue'
    LIMIT 1000
    
    
    8:24
    Select avg(event_param.value.double_value)
    FROM `namespace.table.app_events_intraday_20161111`
    UNEST(event_dim) as event,
    UNEST(event.params) as event_param
    WHERE event.name = 'adjust_slider' AND event_param.key ='newValue'
    LIMIT 1000
    
    
    8:54
    Select userProp.value.value.string_value as dogOrCat, avg(event_param.value.double_value) as newSliderValue
    FROM `namespace.table.app_events_intraday_20161111`
    UNEST(user_dim.user_properties) as userProp,
    UNEST(event_dim) as event,
    UNEST(event.params) as event_param
    WHERE event.name = 'adjust_slider' AND event_param.key ='newValue' AND userProp.key='dogOrCatPerson'
    GROUPY dogOrCat

tryToQueryLastWeek

SELECT
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) + 6 DAY)) AS previousMonday,
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) DAY)) AS previousSunday,
  COUNT(*) as counter,
  h.name as event
FROM `namespace.app_events_*`, UNNEST(event_dim) as h
WHERE _TABLE_SUFFIX
BETWEEN
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) + 6 DAY)) 
AND 
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL CAST ( FORMAT_DATE("%u", CURRENT_DATE()) as INT64 ) DAY))
GROUP BY event
ORDER BY counter DESC


#### 
SELECT
  COUNT(*) as counter,
  h.name as event
FROM `firebase-analytics-sample-data.android_dataset.app_events_*`, UNNEST(event_dim) as h
WHERE _TABLE_SUFFIX
BETWEEN
  '20160601' AND '20160607'
GROUP BY event
ORDER BY counter DESC 

Union 2 date Range

SELECT
  COUNT(*) as counter,
  h.name as event
FROM `firebase-analytics-sample-data.android_dataset.app_events_*`, UNNEST(event_dim) as h
WHERE _TABLE_SUFFIX
BETWEEN
  '20160601' AND '20160603'
  GROUP BY event

UNION DISTINCT
SELECT
  COUNT(*) as counter,
  g.name as event
FROM `firebase-analytics-sample-data.android_dataset.app_events_*`, UNNEST(event_dim) as g
WHERE _TABLE_SUFFIX
BETWEEN
  '20160604' AND '20160607'
GROUP BY event
ORDER BY counter DESC