Data for an iOS app

/*

Given an iOS app ID get the following fields:

  IOS App ID
  App Name
  Publisher ID
  Unified Publisher ID
  Ticker Symbol
  Daily Active Users
  Downloads
  Country for Downloads
  Date for Downloads

*/

SELECT IOS_APPS.id,
    IOS_APPS.name,
    IOS_APPS.publisher_id,
    UNIFIED_PUBLISHERS.id AS unified_publisher_id,
    UNIFIED_PUBLISHERS.stock_ticker,
    IOS_DAILY_USAGE_ESTIMATES.daily_active_ipad_users,
    IOS_DAILY_USAGE_ESTIMATES.daily_active_iphone_users,
    IOS_SALES_REPORT_ESTIMATES.ipad_downloads,
    IOS_SALES_REPORT_ESTIMATES.iphone_downloads,
    IOS_SALES_REPORT_ESTIMATES.country,
    IOS_SALES_REPORT_ESTIMATES.observation_date
FROM IOS_APPS
  JOIN IOS_PUBLISHERS
    ON IOS_APPS.publisher_id = IOS_PUBLISHERS.id
  JOIN UNIFIED_PUBLISHERS
    ON UNIFIED_PUBLISHERS.id = IOS_PUBLISHERS.unified_publisher_id
  LEFT OUTER JOIN IOS_DAILY_USAGE_ESTIMATES
    ON IOS_APPS.id = IOS_DAILY_USAGE_ESTIMATES.app_id
  LEFT OUTER JOIN IOS_SALES_REPORT_ESTIMATES
    ON IOS_DAILY_USAGE_ESTIMATES.app_id = IOS_SALES_REPORT_ESTIMATES.app_id
      AND IOS_DAILY_USAGE_ESTIMATES.country = IOS_SALES_REPORT_ESTIMATES.country
      AND IOS_DAILY_USAGE_ESTIMATES.observation_date = IOS_SALES_REPORT_ESTIMATES.observation_date
WHERE IOS_APPS.id = INSERT_APP_ID_HERE
LIMIT 10;

Data for an Android app

/*

Given an android app ID get the following fields:

  Android App ID
  App Name
  Publisher ID
  Unified Publisher ID
  Ticker Symbol
  Daily Active Users
  Downloads
  Country for Downloads
  Date for Downloads

*/

SELECT ANDROID_APPS.id,
    ANDROID_APPS.name,
    ANDROID_APPS.publisher_id,
    UNIFIED_PUBLISHERS.id AS unified_publisher_id,
    UNIFIED_PUBLISHERS.stock_ticker,
    ANDROID_DAILY_USAGE_ESTIMATES.daily_active_users,
    ANDROID_SALES_REPORT_ESTIMATES.android_downloads,
    ANDROID_SALES_REPORT_ESTIMATES.country,
    ANDROID_SALES_REPORT_ESTIMATES.observation_date
FROM ANDROID_APPS
  JOIN ANDROID_PUBLISHERS
    ON ANDROID_APPS.publisher_id = ANDROID_PUBLISHERS.id
  JOIN UNIFIED_PUBLISHERS
    ON UNIFIED_PUBLISHERS.id = ANDROID_PUBLISHERS.unified_publisher_id
  LEFT OUTER JOIN ANDROID_DAILY_USAGE_ESTIMATES
    ON ANDROID_APPS.id = ANDROID_DAILY_USAGE_ESTIMATES.app_id
  LEFT OUTER JOIN ANDROID_SALES_REPORT_ESTIMATES
    ON ANDROID_DAILY_USAGE_ESTIMATES.app_id = ANDROID_SALES_REPORT_ESTIMATES.app_id
      AND ANDROID_DAILY_USAGE_ESTIMATES.country = ANDROID_SALES_REPORT_ESTIMATES.country
      AND ANDROID_DAILY_USAGE_ESTIMATES.observation_date = ANDROID_SALES_REPORT_ESTIMATES.observation_date
WHERE ANDROID_APPS.id = 'ANDROID_APP_ID_HERE'
LIMIT 10;

Metadata for a Unified app

/*

Retrieve all ios metadata and unified app metadata for a given unified app ID.

*/


SELECT *
  FROM IOS_APPS
  JOIN UNIFIED_APPS
    ON IOS_APPS.unified_app_id = UNIFIED_APPS.id
  WHERE IOS_APPS.unified_app_id = '55d3a1a802ac64350a000d6e';

Data for all public companies

/*

Get all unified publishers with ticker symbols along with all iOS and Android publishers under each unified publisher.

*/


SELECT unified.ID AS UNIFIED_PUBLISHER_ID,
    unified.NAME AS UNIFIED_PUBLISHER_NAME,
    unified.STOCK_TICKER AS TICKER_SYMBOL,
    ios.ID AS IOS_PUBLISHER_ID,
    ios.NAME AS IOS_PUBLISHER_NAME,
    android.ID as ANDROID_PUBLISHER_ID,
    android.NAME as ANDROID_PUBLISHER_NAME
FROM UNIFIED_PUBLISHERS AS unified
    JOIN IOS_PUBLISHERS AS ios
        ON unified.ID = ios.UNIFIED_PUBLISHER_ID
    JOIN ANDROID_PUBLISHERS AS android
        ON unified.ID = android.UNIFIED_PUBLISHER_ID
WHERE unified.STOCK_TICKER IS NOT NULL

Data for all iOS apps and Android apps by device

/*
Downloads, revenue, daily active users, and app metadata for all ios and android apps.
*/

SELECT
    CPA.ID,
    CPA.NAME,
    CPA.UNIFIED_APP_ID,
    CPA.PUBLISHER_ID,
    CPA.PLATFORM,
    CPSRE.OBSERVATION_DATE,
    CPSRE.COUNTRY,
    CPSRE.DOWNLOADS,
    CPSRE.REVENUE,
    CPSRE.IPHONE_DOWNLOADS,
    CPSRE.IPAD_DOWNLOADS,
    CPSRE.IPHONE_REVENUE,
    CPSRE.IPAD_REVENUE,
    CPSRE.REVISION_DATE AS DOWNLOADS_REVENUE_REVISION_DATE,
    CPDUE.OBSERVATION_DATE,
    CPDUE.COUNTRY,
    CPDUE.DAILY_ACTIVE_USERS,
    CPDUE.DAILY_ACTIVE_IPHONE_USERS,
    CPDUE.DAILY_ACTIVE_IPAD_USERS
FROM CROSS_PLATFORM_APPS AS CPA
    JOIN CROSS_PLATFORM_SALES_REPORT_ESTIMATES AS CPSRE
        ON CPA.ID = CPSRE.APP_ID
    JOIN CROSS_PLATFORM_DAILY_USAGE_ESTIMATES AS CPDUE
        ON CPSRE.APP_ID = CPDUE.APP_ID
            AND CPSRE.OBSERVATION_DATE = CPDUE.OBSERVATION_DATE
            AND CPSRE.COUNTRY = CPDUE.COUNTRY

Quarterly download and revenue estimates from publicly-traded companies

/*
Quarterly download and revenue estimates given a single or multiple stock tickers.
*/

SELECT
    STOCK_TICKER,
    YEAR,
    QUARTER,
    SUM(DOWNLOADS) AS TOTAL_DOWNLOADS,
    SUM(REVENUE) AS TOTAL_REVENUE
FROM (
(
SELECT
    UP.STOCK_TICKER AS STOCK_TICKER,
    UP.ID AS UNIFIED_PUBLISHER_ID,
    UP.NAME AS UNIFIED_PUBLISHER_NAME,
    IP.ID AS PLATFORM_PUBLISHER_ID,
    IP.NAME AS PLATFORM_PUBLISHER_NAME,
    IA.ID AS PLATFORM_APP_ID,
    IA.NAME AS PLATFORM_APP_NAME,
    'ios' AS PLATFORM,
    YEAR(ISRE.OBSERVATION_DATE) AS YEAR,
    QUARTER(ISRE.OBSERVATION_DATE) AS QUARTER,
    ISRE.COUNTRY,
    SUM(ISRE.IPHONE_DOWNLOADS) + SUM(ISRE.IPAD_DOWNLOADS) AS DOWNLOADS,
    SUM(ISRE.IPHONE_REVENUE) + SUM(IPAD_REVENUE) AS REVENUE
FROM UNIFIED_PUBLISHERS AS UP
    JOIN IOS_PUBLISHERS AS IP
        ON IP.UNIFIED_PUBLISHER_ID = UP.ID
    JOIN IOS_APPS AS IA
        ON IA.PUBLISHER_ID = IP.ID
    JOIN IOS_SALES_REPORT_ESTIMATES AS ISRE
        ON ISRE.APP_ID = IA.ID
WHERE UP.STOCK_TICKER IN (
    'NASDAQ: AAL',
    'NASDAQ: GRPN',
    'NASDAQ: TSLA'
)
GROUP BY
    UP.STOCK_TICKER,
    UP.ID,
    UP.NAME,
    IP.ID,
    IP.NAME,
    IA.ID,
    IA.NAME,
    YEAR(ISRE.OBSERVATION_DATE),
    QUARTER(ISRE.OBSERVATION_DATE),
    ISRE.COUNTRY
ORDER BY
    UP.STOCK_TICKER,
    IA.ID,
    YEAR(ISRE.OBSERVATION_DATE),
    QUARTER(ISRE.OBSERVATION_DATE)
    ASC
)
UNION
(
SELECT
    UP.STOCK_TICKER AS STOCK_TICKER,
    UP.ID AS UNIFIED_PUBLISHER_ID,
    UP.NAME AS UNIFIED_PUBLISHER_NAME,
    AP.ID AS PLATFORM_PUBLISHER_ID,
    AP.NAME AS PLATFORM_PUBLISHER_NAME,
    AA.ID AS PLATFORM_APP_ID,
    AA.NAME AS PLATFORM_APP_NAME,
    'android' AS PLATFORM,
    YEAR(ASRE.OBSERVATION_DATE) AS YEAR,
    QUARTER(ASRE.OBSERVATION_DATE) AS QUARTER,
    ASRE.COUNTRY,
    SUM(ASRE.ANDROID_DOWNLOADS) AS DOWNLOADS,
    SUM(ASRE.ANDROID_REVENUE) AS REVENUE
FROM UNIFIED_PUBLISHERS AS UP
    JOIN ANDROID_PUBLISHERS AS AP
        ON AP.UNIFIED_PUBLISHER_ID = UP.ID
    JOIN ANDROID_APPS AS AA
        ON AA.PUBLISHER_ID = AP.ID
    JOIN ANDROID_SALES_REPORT_ESTIMATES AS ASRE
        ON ASRE.APP_ID = AA.ID
WHERE UP.STOCK_TICKER IN (
    'NASDAQ: AAL',
    'NASDAQ: GRPN',
    'NASDAQ: TSLA'
)
GROUP BY
    UP.STOCK_TICKER,
    UP.ID,
    UP.NAME,
    AP.ID,
    AP.NAME,
    AA.ID,
    AA.NAME,
    YEAR(ASRE.OBSERVATION_DATE),
    QUARTER(ASRE.OBSERVATION_DATE),
    ASRE.COUNTRY
ORDER BY
    UP.STOCK_TICKER,
    AA.ID,
    YEAR(ASRE.OBSERVATION_DATE),
    QUARTER(ASRE.OBSERVATION_DATE)
    ASC
)
  )
GROUP BY
    STOCK_TICKER,
    YEAR,
    QUARTER
ORDER BY
    STOCK_TICKER,
    YEAR,
    QUARTER
    ASC