Skip to content

Instantly share code, notes, and snippets.

@ddikman
Created Sep 26, 2021
Embed
What would you like to do?
Example of getting an average amount of events per unique user
SELECT
all_users.platform,
all_users.event_date,
views.item_views / all_users.unique_users as searches_per_user
FROM (
SELECT
platform,
count(distinct(user_pseudo_id)) as unique_users,
event_date
FROM `<project-id>.analytics_<id>.events_*`
GROUP BY event_date, platform
) as all_users
LEFT JOIN (
SELECT
platform,
count(*) as item_views,
event_date
FROM `<project-id>.analytics_<id>.events_*`
WHERE event_name = 'search'
GROUP BY event_date, platform
) as views on views.platform = all_users.platform and views.event_date = all_users.event_date
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment