Untitled

#creating the install table
WITH
  install_table AS (
    SELECT
      user_pseudo_id,
      event_name,
      event_date,
      event_timestamp
    FROM `ios-mi-good-or-bad-moms-life.analytics_387432338.events_*`
    WHERE
      event_name = 'first_open'
      AND platform = 'IOS'
  ),
  #creating the revenue table
  revenue_table AS (
    SELECT
      user_pseudo_id,
      event_name,
      event_timestamp,
      EXTRACT(date FROM Parse_datetime('%Y%m%d', event_date)) AS event_date,
      (
        SELECT COALESCE(value.int_value, value.float_value, value.double_value, NULL)
        FROM UNNEST(event_params)
        WHERE
          KEY = 'value'
          AND event_name = 'paid_ad_impression'
      ) AS ad_funded_revenue,
      (
        SELECT value.string_value
        FROM UNNEST(event_params)
        WHERE
          KEY = 'currency'
          AND event_name = 'paid_ad_impression'
      ) AS ad_revenue_currency,
      (
        CASE
          WHEN event_name = 'in_app_purchase' THEN event_value_in_usd
          ELSE 0
          END) AS iap_revenue_usd,
    FROM `ios-mi-good-or-bad-moms-life.analytics_387432338.events_*`
    WHERE
      platform = 'IOS'
      AND event_name IN (
        'in_app_purchase',
        'paid_ad_impression')
  ),
  total_revenue_table AS (
    SELECT
      it.user_pseudo_id AS user_pseudo_id,
      rt.event_date,
      #combine ad revenue and IAP revenue, assuming both are in same currency
      sum(ifnull(rt.iap_revenue_usd,0) + ifnull(rt.ad_funded_revenue,0)) AS total_revenue,
    FROM install_table it
    INNER JOIN revenue_table rt
      ON it.user_pseudo_id = rt.user_pseudo_id
    WHERE
      rt.event_timestamp >= it.event_timestamp
      AND rt.event_timestamp
        <= it.event_timestamp + 86400000000 * 2  #added 86400 000 millisecond as 24 hours
    GROUP BY 1, 2
  )
SELECT approx_quantiles(total_revenue, 64) AS buckets FROM total_revenue_table