snowflake_cross_platform_views.md

CROSS_PLATFORM_APPS

SELECT *
FROM (
  SELECT ID,
         NAME,
         UNIFIED_APP_ID,
         PUBLISHER_ID,
         'ios' AS PLATFORM
  FROM IOS_APPS
) UNION (
  SELECT ID,
         NAME,
         UNIFIED_APP_ID,
         PUBLISHER_ID,
         'android' AS PLATFORM
  FROM ANDROID_APPS
)

CROSS_PLATFORM_DAILY_USAGE_ESTIMATES

SELECT *
FROM (
  (
    SELECT APP_ID,
           OBSERVATION_DATE,
           COUNTRY,
           DAILY_ACTIVE_IPHONE_USERS + DAILY_ACTIVE_IPAD_USERS AS DAILY_ACTIVE_USERS,
           DAILY_ACTIVE_IPHONE_USERS,
           DAILY_ACTIVE_IPAD_USERS,
           'ios' AS PLATFORM
    FROM IOS_DAILY_USAGE_ESTIMATES
  ) UNION (
    SELECT APP_ID,
           OBSERVATION_DATE,
           COUNTRY,
           DAILY_ACTIVE_USERS,
           0 AS DAILY_ACTIVE_IPHONE_USERS,
           0 AS DAILY_ACTIVE_IPAD_USERS,
           'android' AS PLATFORM
    FROM ANDROID_DAILY_USAGE_ESTIMATES
  )
)

CROSS_PLATFORM_WEEKLY_USAGE_ESTIMATES

SELECT *
FROM (
  (
    SELECT APP_ID,
           OBSERVATION_DATE,
           COUNTRY,
           WEEKLY_ACTIVE_IPHONE_USERS + WEEKLY_ACTIVE_IPAD_USERS AS WEEKLY_ACTIVE_USERS,
           WEEKLY_ACTIVE_IPHONE_USERS,
           WEEKLY_ACTIVE_IPAD_USERS,
           'ios' AS PLATFORM
    FROM IOS_WEEKLY_USAGE_ESTIMATES
  ) UNION (
    SELECT APP_ID,
           OBSERVATION_DATE,
           COUNTRY,
           WEEKLY_ACTIVE_USERS,
           0 AS WEEKLY_ACTIVE_IPHONE_USERS,
           0 AS WEEKLY_ACTIVE_IPAD_USERS,
           'android' AS PLATFORM
    FROM ANDROID_WEEKLY_USAGE_ESTIMATES
  )
)

CROSS_PLATFORM_MONTHLY_USAGE_ESTIMATES

SELECT *
FROM (
  (
    SELECT APP_ID,
           OBSERVATION_DATE,
           COUNTRY,
           MONTHLY_ACTIVE_IPHONE_USERS + MONTHLY_ACTIVE_IPAD_USERS AS MONTHLY_ACTIVE_USERS,
           MONTHLY_ACTIVE_IPHONE_USERS,
           MONTHLY_ACTIVE_IPAD_USERS,
           'ios' AS PLATFORM
    FROM IOS_MONTHLY_USAGE_ESTIMATES
  ) UNION (
    SELECT APP_ID,
           OBSERVATION_DATE,
           COUNTRY,
           MONTHLY_ACTIVE_USERS,
           0 AS MONTHLY_ACTIVE_IPHONE_USERS,
           0 AS MONTHLY_ACTIVE_IPAD_USERS,
           'android' AS PLATFORM
    FROM ANDROID_MONTHLY_USAGE_ESTIMATES
  )
)

CROSS_PLATFORM_PUBLISHERS

SELECT *
FROM (
  (
    SELECT ID,
           NAME,
           UNIFIED_PUBLISHER_ID,
           REVISION_DATE,
           'ios' AS PLATFORM
    FROM IOS_PUBLISHERS
  ) UNION (
    SELECT ID,
           NAME,
           UNIFIED_PUBLISHER_ID,
           REVISION_DATE,
           'android' AS PLATFORM
    FROM ANDROID_PUBLISHERS
  )
)

CROSS_PLATFORM_SALES_REPORT_ESTIMATES

SELECT *
FROM (
  (
    SELECT APP_ID,
           OBSERVATION_DATE,
           COUNTRY,
           IPHONE_DOWNLOADS + IPAD_DOWNLOADS AS DOWNLOADS,
           IPHONE_REVENUE + IPAD_REVENUE AS REVENUE,
           IPHONE_DOWNLOADS,
           IPAD_DOWNLOADS,
           IPHONE_REVENUE,
           IPAD_REVENUE,
           REVISION_DATE,
           'ios' AS PLATFORM
    FROM IOS_SALES_REPORT_ESTIMATES
  ) UNION (
    SELECT APP_ID,
           OBSERVATION_DATE,
           COUNTRY,
           ANDROID_DOWNLOADS AS DOWNLOADS,
           ANDROID_REVENUE AS REVENUE,
           0 AS IPHONE_DOWNLOADS,
           0 AS IPAD_DOWNLOADS,
           0 AS IPHONE_REVENUE,
           0 AS IPAD_REVENUE,
           REVISION_DATE,
           'android' AS PLATFORM
    FROM ANDROID_SALES_REPORT_ESTIMATES
  )
)

CROSS_PLATFORM_TIME_SPENT_ESTIMATES

SELECT *
FROM (
  (
    SELECT APP_ID,
           COUNTRY,
           OBSERVATION_DATE,
           MONTHLY_TIME_SPENT,
           QUARTERLY_TIME_SPENT,
           REVISION_DATE,
           'ios' AS PLATFORM
    FROM IOS_TIME_SPENT_ESTIMATES
  ) UNION (
    SELECT APP_ID,
           COUNTRY,
           OBSERVATION_DATE,
           MONTHLY_TIME_SPENT,
           QUARTERLY_TIME_SPENT,
           REVISION_DATE,
           'android' AS PLATFORM
    FROM ANDROID_TIME_SPENT_ESTIMATES
  )
)

CROSS_PLATFORM_SESSION_COUNT_ESTIMATES

SELECT *
FROM (
  (
    SELECT APP_ID,
           COUNTRY,
           OBSERVATION_DATE,
           MONTHLY_SESSION_COUNT,
           QUARTERLY_SESSION_COUNT,
           REVISION_DATE,
           'ios' AS PLATFORM
    FROM IOS_SESSION_COUNT_ESTIMATES
  ) UNION (
    SELECT APP_ID,
           COUNTRY,
           OBSERVATION_DATE,
           MONTHLY_SESSION_COUNT,
           QUARTERLY_SESSION_COUNT,
           REVISION_DATE,
           'android' AS PLATFORM
    FROM ANDROID_SESSION_COUNT_ESTIMATES
  )
)