Apple: Apps Breaking Into Their Category's Top Three

Hard
620.

Apple: Apps Breaking Into Their Category's Top Three

App Store editorial features apps that have just climbed into the top of their category's monthly download chart.

In each category and month, rank apps by downloads, highest first, with RANK() semantics (tied apps share a position and the next position is skipped). An app breaks into the top three in a month when its position that month is 3 or better and, in the previous calendar month, it either had a position worse than 3 or no row at all. Skip the earliest month present in the whole table, which has nothing to compare with. Return category, month_start, app_name and rank_now, ordered by month_start, category, rank_now, then app_name.

A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.

Tables

Table: ChartApp

ColumnType
app_idint
app_namevarchar
categoryvarchar

Primary key: app_id.

One row per app; names are unique.

Table: MonthlyDownloads

ColumnType
app_idint
month_startdate
downloadsint

Primary key (together): app_id, month_start.

Downloads of an app in a month; month_start is the first day of the month. An app with no row in a month is not on that month's chart.

Example

The rows must come back in the order shown.

ChartApp

app_idapp_namecategory
1Pocket TablaMusic
2Raga TunerMusic
3Sketch StudioMusic
4Monsoon WeatherMusic
5Budget BirdMusic

MonthlyDownloads

app_idmonth_startdownloads
12025-01-01900
22025-01-01700
32025-01-01500
42025-01-01300
12025-02-01800
22025-02-01400
42025-02-01650
52025-02-01400
32025-03-011000
52025-03-01200

Output

categorymonth_startapp_namerank_now
Music2025-02-01Monsoon Weather2
Music2025-02-01Budget Bird3
Music2025-03-01Sketch Studio1
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Apple: Apps Breaking Into Their Category's Top Three

App Store editorial features apps that have just climbed into the top of their category's monthly download chart.

  • Difficulty: Hard
  • Topics: Window Functions, Dates, Joins
  • Dialect: MySQL
  • Problem: #620

Problem statement

App Store editorial features apps that have just climbed into the top of their category's monthly download chart.

In each category and month, rank apps by downloads, highest first, with RANK() semantics (tied apps share a position and the next position is skipped). An app breaks into the top three in a month when its position that month is 3 or better and, in the previous calendar month, it either had a position worse than 3 or no row at all. Skip the earliest month present in the whole table, which has nothing to compare with. Return category, month_start, app_name and rank_now, ordered by month_start, category, rank_now, then app_name.

A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.

Tables

Table: ChartApp

ColumnType
app_idint
app_namevarchar
categoryvarchar

Primary key: app_id.

One row per app; names are unique.

Table: MonthlyDownloads

ColumnType
app_idint
month_startdate
downloadsint

Primary key: app_id, month_start.

Downloads of an app in a month; month_start is the first day of the month. An app with no row in a month is not on that month's chart.

Examples

Example 1

ChartApp

app_idapp_namecategory
1Pocket TablaMusic
2Raga TunerMusic
3Sketch StudioMusic
4Monsoon WeatherMusic
5Budget BirdMusic

MonthlyDownloads

app_idmonth_startdownloads
12025-01-01900
22025-01-01700
32025-01-01500
42025-01-01300
12025-02-01800
22025-02-01400
42025-02-01650
52025-02-01400
32025-03-011000
52025-03-01200

Output

categorymonth_startapp_namerank_now
Music2025-02-01Monsoon Weather2
Music2025-02-01Budget Bird3
Music2025-03-01Sketch Studio1

How to solve Apple: Apps Breaking Into Their Category's Top Three

Start by building the monthly charts: RANK() OVER (PARTITION BY category, month_start ORDER BY downloads DESC). RANK (not ROW_NUMBER) is what the statement asks for — two apps tied for third are both third, and the app after them is fifth, so a tie can put four apps in the top three.

Then compare each app with itself a month earlier. Joining the ranked rows to themselves on app_id and month_no − 1 (with month_no = YEAR × 12 + MONTH, so December → January works) gives the previous position. It must be a LEFT join: an app with no row last month was off the chart, and that counts as breaking in. The condition is then pos <= 3 AND (no previous row OR previous pos > 3). A month directly after a gap has no previous-month rows for anyone, so all its top apps count — but the very first month in the table is excluded explicitly, since there is nothing to compare it with.

The alternative computes the rank as "1 + apps in the category with more downloads that month" — the definition of RANK — and uses NOT EXISTS for "was in the top three last month". The window version sorts each category-month once; the subquery version is quadratic per chart.

Reference solution (MySQL)

WITH ranked AS (
  SELECT a.app_id, a.app_name, a.category, d.month_start,
         YEAR(d.month_start) * 12 + MONTH(d.month_start) AS month_no,
         RANK() OVER (PARTITION BY a.category, d.month_start ORDER BY d.downloads DESC) AS pos
  FROM MonthlyDownloads d
  JOIN ChartApp a ON a.app_id = d.app_id
)
SELECT c.category, c.month_start, c.app_name, c.pos AS rank_now
FROM ranked c
LEFT JOIN ranked p ON p.app_id = c.app_id AND p.month_no = c.month_no - 1
WHERE c.pos <= 3
  AND c.month_start > (SELECT MIN(month_start) FROM MonthlyDownloads)
  AND (p.app_id IS NULL OR p.pos > 3)
ORDER BY c.month_start, c.category, rank_now, c.app_name

Another way

WITH ranked AS (
  SELECT a.app_id, a.app_name, a.category, d.month_start,
         (SELECT COUNT(*) FROM MonthlyDownloads d2 JOIN ChartApp a2 ON a2.app_id = d2.app_id
          WHERE a2.category = a.category AND d2.month_start = d.month_start AND d2.downloads > d.downloads) + 1 AS pos
  FROM MonthlyDownloads d
  JOIN ChartApp a ON a.app_id = d.app_id
)
SELECT c.category, c.month_start, c.app_name, c.pos AS rank_now
FROM ranked c
WHERE c.pos <= 3
  AND EXISTS (SELECT 1 FROM MonthlyDownloads e WHERE e.month_start < c.month_start)
  AND NOT EXISTS (SELECT 1 FROM ranked p WHERE p.app_id = c.app_id AND p.pos <= 3
                  AND p.month_start = DATE_SUB(c.month_start, INTERVAL 1 MONTH))
ORDER BY c.month_start, c.category, rank_now, c.app_name

← Apple: Devices That Moved to an Older iOS Version · Apple: Launch-Day Pre-Orders Filled Strictly in Queue Order →