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