#standardSQL

# This query will get the average and standard deviation of a "score" parameter that's part of a
# "round_completed" event. Feel free to replace these values to match events and parameters that are
# already in your app.

SELECT
  AVG(param.value.int_value) AS avg_score,
  stddev(param.value.int_value) AS stddev
FROM
  `<dataset>.app_events_<date>`,
  UNNEST(event_dim) AS event,
  UNNEST(event.params) AS param
WHERE
  event.name = "round_completed"
  AND param.key = "score"

#standardSQL

# Get the correlation between one event parameter (in this case, the "score" parameter of the
# "round_completed" event and a user property (in this case, the "xp" user property, converted
# from a string to an int).

SELECT
  corr(score, xp)
FROM (
  SELECT
    event.name,
    param.value.int_value AS score,
    CAST(user_prop.value.value.string_value AS int64) AS xp
  FROM
    `<dataset>.app_events_<date>`,
    UNNEST(event_dim) AS event,
    UNNEST(event.params) AS param,
    UNNEST(user_dim.user_properties) AS user_prop
  WHERE
    event.name = "round_completed"
    AND param.key = "score"
    AND user_prop.key = "xp" )
 @ToddKerpelman
  
            
 
Write