ga4.sql

gistfile1.txt

SELECT ANY_VALUE(board_type) as board, AVG(score) as average_score
FROM  (
  SELECT MAX(if(param.key = "value", param.value.int_value, NULL)) AS score,
    MAX(if(param.key = "board", param.value.string_value, NULL)) AS board_type
  FROM (
    SELECT user_pseudo_id, event_timestamp, param
    FROM `firebase-public-project.analytics_153293282.events_20180915`,
    UNNEST(event_params) AS param
    WHERE event_name = "level_complete_quickplay"
    AND (param.key = "value" OR param.key = "board")
  )
  GROUP BY user_pseudo_id, event_timestamp
) 
GROUP BY board_type