#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" )