Skip to content

Instantly share code, notes, and snippets.

@janbenetka

janbenetka/norm_counts.sql

Created October 15, 2021 09:10
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save janbenetka/e662c18829e824aeaa21a249abeb7341 to your computer and use it in GitHub Desktop.
Save janbenetka/e662c18829e824aeaa21a249abeb7341 to your computer and use it in GitHub Desktop.
Download ZIP
[Normalized Counts per Group] #bigquery #over
Raw
norm_counts.sql
WITH daily AS (
SELECT
local_event_date,
venue_type,
SUM(device_count) device_count,
SUM(person_count) person_count,
FROM `uc-prox-core-dev.international_metrics.traffic_trends`
GROUP BY local_event_date, venue_type
)
SELECT
local_event_date,
venue_type,
device_count / AVG(device_count) OVER (PARTITION BY venue_type) as device_count,
person_count / AVG(person_count) OVER (PARTITION BY venue_type) as person_count,
FROM daily
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment