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
| Column | Type |
|---|---|
| app_id | int |
| app_name | varchar |
| category | varchar |
Primary key: app_id.
One row per app; names are unique.
Table: MonthlyDownloads
| Column | Type |
|---|---|
| app_id | int |
| month_start | date |
| downloads | int |
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_id | app_name | category |
|---|---|---|
| 1 | Pocket Tabla | Music |
| 2 | Raga Tuner | Music |
| 3 | Sketch Studio | Music |
| 4 | Monsoon Weather | Music |
| 5 | Budget Bird | Music |
MonthlyDownloads
| app_id | month_start | downloads |
|---|---|---|
| 1 | 2025-01-01 | 900 |
| 2 | 2025-01-01 | 700 |
| 3 | 2025-01-01 | 500 |
| 4 | 2025-01-01 | 300 |
| 1 | 2025-02-01 | 800 |
| 2 | 2025-02-01 | 400 |
| 4 | 2025-02-01 | 650 |
| 5 | 2025-02-01 | 400 |
| 3 | 2025-03-01 | 1000 |
| 5 | 2025-03-01 | 200 |
Output
| category | month_start | app_name | rank_now |
|---|---|---|---|
| Music | 2025-02-01 | Monsoon Weather | 2 |
| Music | 2025-02-01 | Budget Bird | 3 |
| Music | 2025-03-01 | Sketch Studio | 1 |
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_nameAnother 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 →