WITH digit as (select 0 as d
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9),
HUNDRED as (
  select 1 + a.d + (10 * b.d) as num
  from digit a
  cross join digit b
  order by 1),
HUNDRED_DAYS AS (
  SELECT date_add(current_date, INTERVAL -num day) AS dt
  FROM HUNDRED),
CLEAN_USER_TABLE AS (
  SELECT
    FILTERED_USER_TABLE.UDID,
    FILTERED_USER_TABLE.game,
    FILTERED_USER_TABLE.install_date,
    GEO_BUCKET.geo,
    CASE
      WHEN HARDWARE.device_type IS NULL THEN 'other'
      ELSE HARDWARE.device_type
      END AS device_type,
    CASE
      WHEN INSTALL_CHANNEL.channel IS NULL THEN 'Organic'
      ELSE 'Paid'
      END AS channel
  FROM
    physical.users AS FILTERED_USER_TABLE
    LEFT JOIN (
      SELECT
      -- do we need distinct since each udid should only have one channel anyways?
        DISTINCT game, channel, udid
      FROM
        physical.channelclaims)
    INSTALL_CHANNEL
    ON (
      INSTALL_CHANNEL.game = FILTERED_USER_TABLE.game AND
      FILTERED_USER_TABLE.udid = INSTALL_CHANNEL.udid
    )
    LEFT JOIN
    public.hardwareequiv HARDWARE
    ON (
      FILTERED_USER_TABLE.hw_ver = HARDWARE.hw_ver
    )
    LEFT JOIN (
      SELECT
        geo_bucket AS geo, ip_country
      FROM
        public.geo_buckets)
    GEO_BUCKET
    ON (
      FILTERED_USER_TABLE.ip_country = GEO_BUCKET.ip_country
      )
    ),
ACTIVE_SESSION AS (
  SELECT
    CLEAN_USER_TABLE.game,
    sess.date,
    CLEAN_USER_TABLE.geo,
    CLEAN_USER_TABLE.channel,
    CLEAN_USER_TABLE.device_type,
    CASE
      WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) = 0 THEN '(0) 0'
      WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) = 1 THEN '(1) 1'
      WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 2 AND 6 THEN '(2) 2-6'
      WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 7 AND 14 THEN '(3) 7-14'
      WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 15 AND 30 THEN '(4) 15-30'
      WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 31 AND 60 THEN '(5) 31-60'
      WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 61 AND 90 THEN '(6) 61-90'
      WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) > 90 THEN '(7) 90 plus'
      END AS cohortbucket,
    COUNT(DISTINCT sess.udid) AS daus
  FROM
    physical.sessions as sess
    LEFT JOIN CLEAN_USER_TABLE
    ON
      sess.game = CLEAN_USER_TABLE.game AND
      sess.udid = CLEAN_USER_TABLE.udid
  WHERE
    sess.date BETWEEN date_sub(current_date, INTERVAL 100 day) AND date_sub(current_date, INTERVAL 1 day)
  GROUP BY
    CLEAN_USER_TABLE.game,
    sess.date,
    CLEAN_USER_TABLE.geo,
    CLEAN_USER_TABLE.channel,
    CLEAN_USER_TABLE.device_type,
    cohortbucket
),
IAPS AS (
  SELECT
    CLEAN_USER_TABLE.game,
    iaps.date,
    CLEAN_USER_TABLE.geo,
    CLEAN_USER_TABLE.channel,
    CLEAN_USER_TABLE.device_type,
    CASE
      WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) = 0 THEN '(0) 0'
      WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) = 1 THEN '(1) 1'
      WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 2 AND 6 THEN '(2) 2-6'
      WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 7 AND 14 THEN '(3) 7-14'
      WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 15 AND 30 THEN '(4) 15-30'
      WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 31 AND 60 THEN '(5) 31-60'
      WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 61 AND 90 THEN '(6) 61-90'
      WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) > 90 THEN '(7) 90 plus'
      END
      AS cohortbucket,
    sum(rev)/100 AS Revenue,
    COUNT(DISTINCT iaps.udid) AS payers
  FROM
    physical.iaps as iaps
    LEFT JOIN
    CLEAN_USER_TABLE
    ON
      iaps.game = CLEAN_USER_TABLE.game AND
      iaps.udid = CLEAN_USER_TABLE.udid
  WHERE
    iaps.date BETWEEN date_sub(current_date, INTERVAL 100 day) AND date_sub(current_date, INTERVAL 1 day)
  GROUP BY
    CLEAN_USER_TABLE.game,
    iaps.date,
    CLEAN_USER_TABLE.geo,
    CLEAN_USER_TABLE.channel,
    CLEAN_USER_TABLE.device_type,
    cohortbucket
),
INSTALL_COHORT AS (
  SELECT
    INSTALL_COUNT.game,
    HUNDRED_DAYS.dt AS date,
    INSTALL_COUNT.geo,
    INSTALL_COUNT.channel,
    INSTALL_COUNT.device_type,
    CASE
      WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) = 0 THEN '(0) 0'
      WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) = 1 THEN '(1) 1'
      WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) BETWEEN 2 AND 6 THEN '(2) 2-6'
      WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) BETWEEN 7 AND 14 THEN '(3) 7-14'
      WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) BETWEEN 15 AND 30 THEN '(4) 15-30'
      WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) BETWEEN 31 AND 60 THEN '(5) 31-60'
      WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) BETWEEN 61 AND 90 THEN '(6) 61-90'
      WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) > 90 THEN '(7) 90 plus'
      END
      AS cohortbucket,
    SUM(installs) AS cohort_install
  FROM (
    SELECT
      CLEAN_USER_TABLE.game,
      CLEAN_USER_TABLE.install_date AS install_dt,
      CLEAN_USER_TABLE.geo,
      CLEAN_USER_TABLE.channel,
      CLEAN_USER_TABLE.device_type,
      COUNT(distinct udid) as installs
    FROM
      CLEAN_USER_TABLE
    GROUP BY
      game, install_dt, geo, channel, device_type
  ) AS INSTALL_COUNT
  CROSS JOIN
  HUNDRED_DAYS
  GROUP BY
    INSTALL_COUNT.game,
    HUNDRED_DAYS.dt,
    INSTALL_COUNT.geo,
    INSTALL_COUNT.channel,
    INSTALL_COUNT.device_type,
    cohortbucket
)

SELECT SESS_IAP.game AS game,
      SESS_IAP.date AS date,
      SESS_IAP.geo AS geo,
      SESS_IAP.channel AS channel,
      SESS_IAP.device_type AS device_type,
      SESS_IAP.cohortbucket AS cohortbuckets,
      INSTALL_COHORT.cohort_install AS installs,
      SESS_IAP.daus    AS daus,
      SESS_IAP.payers  AS payers,
      SESS_IAP.Revenue AS Revenue
FROM (
  SELECT
    ACTIVE_SESSION.game,
    ACTIVE_SESSION.date,
    ACTIVE_SESSION.geo,
    ACTIVE_SESSION.channel,
    ACTIVE_SESSION.device_type,
    ACTIVE_SESSION.cohortbucket,
    SUM(ACTIVE_SESSION.daus) AS daus,
    SUM(IAPS.payers) AS payers,
    SUM(IAPS.Revenue) AS Revenue
  FROM
    ACTIVE_SESSION
    FULL OUTER JOIN
    IAPS
    ON (
      ACTIVE_SESSION.game = IAPS.game AND
      ACTIVE_SESSION.date = IAPS.date AND
      ACTIVE_SESSION.geo = IAPS.geo AND
      ACTIVE_SESSION.channel = IAPS.channel AND
      ACTIVE_SESSION.device_type = IAPS.device_type AND
      ACTIVE_SESSION.cohortbucket = IAPS.cohortbucket
    )
    GROUP BY
      ACTIVE_SESSION.game,
      ACTIVE_SESSION.date,
      ACTIVE_SESSION.geo,
      ACTIVE_SESSION.channel,
      ACTIVE_SESSION.device_type,
      ACTIVE_SESSION.cohortbucket
) AS SESS_IAP
LEFT JOIN
INSTALL_COHORT
ON (
  SESS_IAP.game = INSTALL_COHORT.game AND
  SESS_IAP.date = INSTALL_COHORT.date AND
  SESS_IAP.geo = INSTALL_COHORT.geo AND
  SESS_IAP.channel = INSTALL_COHORT.channel AND
  SESS_IAP.device_type =INSTALL_COHORT.device_type AND
  SESS_IAP.cohortbucket = INSTALL_COHORT.cohortbucket
)
ORDER BY game desc