[Normalized Counts per Group] #bigquery #over

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