Chart Search

Darkpool Chart Search

How does this work?

How to use Darkpool Chart Search

Darkpool Chart Search allows iOS and Android chart rankings to be queried in a SQL-like format. Dark Chart Search only supports SELECT queries, and it doesn't support JOIN statements or sub-queries. Navigate the options below to understand how Darkpool Chart Search can be used.

Query Structure

Chart Types

Chart Categories

Chart Countries

Reserved Keywords

Sample Queries

Query Structure

Darkpool Chart Search processes SQL-like queries to retrieve data. Querying from Darkpool is done like any standard RDBMS, except that Darkpool Chart Search only accommodates the SELECT statement.

Basic Usage

Darkpool Chart Search queries should always query from the darkpool table. The darkpool table is an abstraction; it contains a number of columns that can be queried:

chart_type: the type of chart being queried from, eg. topfreeapplications on iOS, or topselling_free on Android for the relevant Top Downloaded charts
chart_category: the app store category that a given app is published under, eg. 0 on iOS or APPLICATION on Android for All Categores, or 6014 on iOS or GAME on Android for Games.
platform: the platform being queried, which can be either IOS or ANDROID.
name: an app's name
developer: the name of an app's developer
The previous 90 dates in the format of YYYY-mm-dd (eg. 2024-01-01 for January 1st, 2024): Specific dates can be queried, or all dates stored can be queried. Darkpool stores the previous 90 days worth of chart data, including that day. These date columns contain the rank for a given app, based on the various other dimensions supplied (eg. chart_category and country).

As an example, to query the Top 10 Downloaded apps in the Games categories on both iOS and Android in the US on January 1st, 2024, one could write the following query (note no quotes around reserved keywords or date columns):

SELECT 2024-01-01 FROM darkpool WHERE chart_type IN (ANDROID_DOWNLOADED, IOS_DOWNLOADED) AND country='US' AND chart_category IN (ANDROID_GAMES, IOS_GAMES) AND 2024-01-01 <= 10 ORDER BY 2024-01-01 ASC

Reserved Keywords

Reserved keywords exist for some columns. The full list of reserved keywords can be viewed by clicking on the reserved keywords tab in the menu to the left. For instance, instead of typing out all 90 dates to query the full dataset for a given app, the 90_DAYS keyword can be used, which will automatically be interpreted as the list of all 90 date columns in the database. Other keywords exists for chart types (eg. IOS_DOWNLOADED and ANDROID_DOWNLOADED in place of the topfreeapplications and topselling_free chart types that represent the Top Downloaded charts on iOS and Android, respectively). Reserved keywords also exist for chart categories.

Aggregate Functions

Three aggregate functions are available for use in queries:

AVG(): calculates the average value of a set of columns
BEST_RANK(): calculates the best (highest) rank over some period of time. This period of time is provided as either a manually-constructed list of dates or a reserved keyword representing some set of dates, eg., 30_DAYS.
WORST_RANK(): calculates the worst (highest) rank over some period of time. This period of time is provided as either a manually-constructed list of dates or a reserved keyword representing some set of dates, eg., 30_DAYS.

Average functions can take reserved keywords to represent sets of columns. See the Reserved keywords tab for a list of all available reserved keywords.

As an example, to calculate the average rank for the past week (inclusive of the most recent date available) on the Top Downloaded chart in the All Categories category in the US on iOS, where the average rank is less than or equal to 10, the following query can be used (note no quotes around reserved keywords):

SELECT AVG( 7_DAYS ) as avg_7_day_rank FROM darkpool WHERE chart_type = IOS_DOWNLOADED AND country='US' AND chart_category = IOS_OVERALL HAVING avg_7_day_rank <= 10 ORDER BY avg_7_day_rank ASC

Query Processing

Only the SELECT statement is available for use in Darkpool Chart Explorer queries. Performing a JOIN statement is not possible, nor are subqueries. Queries submitted to the Darkpool Chart Explorer are parsed, tokenized, and processed as separate queries to multiple tables. Queries are also recorded per user.

Chart Types

Chart Categories

Chart Countries

Reserved Keywords

Keyword

Replacement Value

30_DAYS

The most recent 30 days' worth of dates, including the most recent date, eg. 30 days ago through today.

PREV_30DAYS

The 30 days prior to the most recent 30 days' worth of dates, eg. 60 days ago through 31 days ago, inclusive.

14_DAYS

The most recent 14 days' worth of dates, including the most recent date, eg. 14 days ago through today.

PREV_14DAYS

The 14 days prior to the most recent 14 days' worth of dates, eg. 28 days ago through 15 days ago, inclusive.

7_DAYS

The most recent 7 days' worth of dates, including the most recent date, eg. 7 days ago through today.

PREV_7DAYS

The 7 days prior to the most recent 7 days' worth of dates, eg. 14 days ago through 8 days ago, inclusive.

3_DAYS

The most recent 3 days' worth of dates, including the most recent date, eg. 3 days ago through today.

PREV_3DAYS

The 3 days prior to the most recent 3 days' worth of dates, eg. 6 days ago through 4 days ago, inclusive.

90_DAYS

The entire 90-day dataset available in Darkpool Chart Explorer.

LAST_DAY

The most recent day's worth of data available in Darkpool Chart Explorer (ie. today)

PREV_DAY

The day before the most recent day's worth of data available in Darkpool Chart Explorer (ie. yesterday)

FIRST_DAY

The first day's worth of data available in Darkpool Chart Explorer (ie. 90 days ago)

IOS_GROSSING

The iOS Top Grossing Chart (substitutes for topgrossingapplications)

IOS_DOWNLOADED

The iOS Top Downloaded Chart (substitutes for topfreeapplications)

ANDROID_GROSSING

The Android Top Grossing Chart (substitutes for the topgrossing chart type)

ANDROID_DOWNLOADED

The Android Top Downloaded Chart (substitutes for the topselling_free chart type)

IOS_OVERALL

The iOS 'All Categories' category (substitutes for the 0 chart category)

ANDROID_OVERALL

The Android 'All Categories' category (substitutes for the APPLICATION chart category)

IOS_GAMES

The iOS 'Games' category (substitutes for the 6014 chart category)

ANDROID_GAMES

The Android 'Games' category (substitutes for the GAME chart category)

Sample Queries

Find a number of sample queries below:

Rolling 7-day Average Top Downloaded Chart Rank Increase on iOS in the US)

SELECT ( AVG( PREV_7DAYS ) - AVG( 7_DAYS ) ) as two_week_rank_change FROM darkpool WHERE chart_type = IOS_DOWNLOADED AND chart_category = IOS_OVERALL AND country = 'US' HAVING two_week_rank_change > 20 ORDER BY two_week_rank_change DESC

10 Apps that have seen their worst rank over the past 90 days above 50 but their best rank over the past 7 days under 10

SELECT name, BEST_RANK(7_DAYS) best_rank, WORST_RANK(90_DAYS) worst_rank, 90_DAYS FROM darkpool WHERE country='US' AND chart_type=IOS_GROSSING AND chart_category=IOS_OVERALL AND WORST_RANK(90_DAYS) > 50 AND BEST_RANK(7_DAYS) < 10 ORDER BY best_rank ASC LIMIT 5

10 Largest One-Day Top Grossing Chart Increase, Yesterday to Today, in the Game and Health & Fitness categories on Android in the UK

SELECT (PREV_DAY - LAST_DAY) as one_day_rank_increase FROM darkpool WHERE chart_type = ANDROID_GROSSING AND chart_category IN (ANDROID_GAME, 'HEALTH_AND_FITNESS') AND country='GB' AND platform='ANDROID' ORDER BY one_day_rank_increase DESC LIMIT 10

All apps in the the Overall chart category on iOS in the US that have been #1 Top Downloaded in the past 90 days, along with their 90 days of rank data

SELECT 90_DAYS, LEAST(90_DAYS) as best_rank FROM darkpool WHERE chart_type = IOS_DOWNLOADED AND country='US' AND chart_category=IOS_OVERALL HAVING best_rank=1 ORDER BY LAST_DAY ASC

Ranks for the Threads app on both iOS and Android, in the Overall category for each, in the US, Canada, United Kingdom, Germany, and France

SELECT CONCAT('Threads - ', country, ' - ', platform) as name, 90_DAYS FROM darkpool WHERE name = 'Threads, an Instagram app' AND country IN( 'US', 'CA', 'GB', 'DE', 'FR' ) AND chart_category IN (IOS_OVERALL, ANDROID_OVERALL) AND chart_type IN (IOS_DOWNLOADED, ANDROID_DOWNLOADED)

Output search results to a chart

Width

Height (inches)

Chart Title

Display Search Query?

Yes

Legend Location

Invert Y-Axis?

Yes

Chart Type