Skip to content

Instantly share code, notes, and snippets.

@TurhanOz

TurhanOz/Get SUM of counts for an event

Last active December 5, 2019 21:27
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save TurhanOz/43b30d99710cb8ae594aa0f0c3bef12f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save TurhanOz/43b30d99710cb8ae594aa0f0c3bef12f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. TurhanOz revised this gist Jan 24, 2017. 1 changed file with 11 additions and 0 deletions.
    11 changes: 11 additions & 0 deletions Get SUM of counts for an event
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,11 @@
    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'
  2. TurhanOz revised this gist Dec 15, 2016. 1 changed file with 9 additions and 0 deletions.
    9 changes: 9 additions & 0 deletions flatten nested data into 1 row each
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,9 @@
    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
  3. TurhanOz revised this gist Dec 15, 2016. 1 changed file with 13 additions and 1 deletion.
    14 changes: 13 additions & 1 deletion average event filter by user property
    View file
    Original file line number Diff line number Diff line change
    @@ -11,4 +11,16 @@ FROM
    UNNEST(user_dim.user_properties) user_properties
    WHERE
    user_properties.key = "language"
    GROUP BY 1;
    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
  4. TurhanOz revised this gist Dec 15, 2016. 1 changed file with 14 additions and 0 deletions.
    14 changes: 14 additions & 0 deletions average event filter by user property
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,14 @@
    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;
  5. TurhanOz revised this gist Dec 15, 2016. 1 changed file with 26 additions and 0 deletions.
    26 changes: 26 additions & 0 deletions Queryies from Youtube Firebase Cast
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,26 @@
    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
  6. TurhanOz revised this gist Dec 13, 2016. 1 changed file with 13 additions and 1 deletion.
    14 changes: 13 additions & 1 deletion tryToQueryLastWeek
    View file
    Original file line number Diff line number Diff line change
    @@ -10,4 +10,16 @@ BETWEEN
    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
    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
  7. TurhanOz revised this gist Dec 13, 2016. 1 changed file with 19 additions and 0 deletions.
    19 changes: 19 additions & 0 deletions Union 2 date Range
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,19 @@
    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
  8. TurhanOz revised this gist Dec 13, 2016. 1 changed file with 7 additions and 4 deletions.
    11 changes: 7 additions & 4 deletions tryToQueryLastWeek
    View file
    Original file line number Diff line number Diff line change
    @@ -1,10 +1,13 @@
    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 `firebase-analytics-sample-data.android_dataset.app_events_*`, UNNEST(event_dim) as h
    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 3
    ORDER BY 3 DESC LIMIT 1
    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
  9. TurhanOz revised this gist Dec 13, 2016. 1 changed file with 10 additions and 0 deletions.
    10 changes: 10 additions & 0 deletions tryToQueryLastWeek
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,10 @@
    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,
    h.name as event
    FROM `firebase-analytics-sample-data.android_dataset.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 3
    ORDER BY 3 DESC LIMIT 1
  10. TurhanOz revised this gist Dec 11, 2016. 1 changed file with 69 additions and 0 deletions.
    69 changes: 69 additions & 0 deletions [BigQuery] get Best Counter _ Standard SQL
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,69 @@
    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
  11. TurhanOz revised this gist Dec 11, 2016. 1 changed file with 20 additions and 0 deletions.
    20 changes: 20 additions & 0 deletions [BigQuery] Last Week Range _ Standard SQL
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,20 @@
    ##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;
  12. TurhanOz revised this gist Dec 11, 2016. 1 changed file with 21 additions and 0 deletions.
    21 changes: 21 additions & 0 deletions [BigQuery] Formating DATE _ Standard SQL
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,21 @@
    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;
  13. TurhanOz revised this gist Dec 11, 2016. 1 changed file with 25 additions and 0 deletions.
    25 changes: 25 additions & 0 deletions [BigQuery] Formating TIMESTAMP _ Standard SQL
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,25 @@
    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;


  14. TurhanOz renamed this gist Dec 11, 2016. 1 changed file with 0 additions and 0 deletions.
    0 Range of Date _ Standard SQL → [BigQuery] Range of Date _ Standard SQL
    View file
    File renamed without changes.
  15. TurhanOz created this gist Dec 11, 2016.
    23 changes: 23 additions & 0 deletions Range of Date _ Standard SQL
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,23 @@
    #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())