Skip to content

Instantly share code, notes, and snippets.

@Thong-Tran

Thong-Tran/latest-user-property.sql

Created December 29, 2020 08:03
Show Gist options
  • Save Thong-Tran/584e70228424916ee6fadbf2079ac044 to your computer and use it in GitHub Desktop.
Save Thong-Tran/584e70228424916ee6fadbf2079ac044 to your computer and use it in GitHub Desktop.
Download ZIP
Custom query for firebase event use in google datastudio
Raw
latest-user-property.sql
SELECT
event_date, event_timestamp, event_name, app_info.id, user_pseudo_id,
max(
if(user_property.key = "user_status", user_property.value.string_value, NULL)
) user_status
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC ) as rn
FROM `project-id.dataset-id.events_*`
WHERE
_TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
),
UNNEST( user_properties ) as user_property
WHERE
rn=1
GROUP BY
event_date, event_timestamp, event_name, app_info.id, user_pseudo_id
Raw
unpack-event-param.sql
SELECT
event_date, event_timestamp, event_name, app_info.id,
max(
if(event_param.key = "item_id", event_param.value.int_value, NULL)
) item_id
FROM
`project-id.dataset-id.events_*`,
UNNEST(event_params) as event_param
WHERE
_TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
GROUP BY
event_date, event_timestamp, event_name, app_info.id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment