Apple: App Store Apps Not Updated Since Mid-2023
Before a store-wide clean-up, App Store editorial lists apps whose developers have stopped shipping updates.
- Difficulty: Easy
- Topics: Aggregation, Dates, Joins
- Dialect: MySQL
- Problem: #603
Problem statement
Before a store-wide clean-up, App Store editorial lists apps whose developers have stopped shipping updates.
Return every app whose most recent release is dated before 2023-07-01, with the columns app_name and last_release_on. An app with any release on or after 2023-07-01 is still maintained and is not listed; every app has at least one release. Return the rows ordered by last_release_on, 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: StoreApp
| Column | Type |
|---|---|
| app_id | int |
| app_name | varchar |
| developer | varchar |
Primary key: app_id.
One row per app on the store; names are unique.
Table: AppRelease
| Column | Type |
|---|---|
| app_id | int |
| version | varchar |
| released_on | date |
Primary key: app_id, version.
One row per version an app shipped.
Examples
Example 1
StoreApp
| app_id | app_name | developer |
|---|---|---|
| 1 | Pocket Tabla | Lotus Labs |
| 2 | Metro Mapper | Banyan Apps |
| 3 | Raga Tuner | Indigo Studio |
| 4 | Tiffin Planner | Neem Tech |
AppRelease
| app_id | version | released_on |
|---|---|---|
| 1 | 1.0 | 2022-03-10 |
| 1 | 1.1 | 2023-06-30 |
| 2 | 2.0 | 2022-11-01 |
| 2 | 2.1 | 2023-07-01 |
| 3 | 1.0 | 2021-08-15 |
| 4 | 3.4 | 2024-02-02 |
Output
| app_name | last_release_on |
|---|---|
| Raga Tuner | 2021-08-15 |
| Pocket Tabla | 2023-06-30 |
How to solve Apple: App Store Apps Not Updated Since Mid-2023
An app is stale when its latest release is old, so the question is about a per-app maximum. Join the apps to their releases, group by app, and take MAX(released_on); the condition MAX(released_on) < '2023-07-01' then goes in HAVING, because it filters groups after they are formed. A WHERE released_on < '2023-07-01' would be wrong: it would throw away the new releases before grouping and make a maintained app look abandoned.
The boundary is strict — a release on 2023-07-01 itself keeps the app off the list. The order is fixed by the statement: oldest last release first, ties by name.
The alternative says the same thing as an anti join: keep apps for which no release on or after the cut-off exists, and fetch the latest date with a correlated subquery. Both scan the releases once with an index on (app_id, released_on).
Reference solution (MySQL)
SELECT a.app_name, MAX(r.released_on) AS last_release_on
FROM StoreApp a
JOIN AppRelease r ON r.app_id = a.app_id
GROUP BY a.app_id, a.app_name
HAVING MAX(r.released_on) < '2023-07-01'
ORDER BY last_release_on, a.app_nameAnother way
SELECT a.app_name, (SELECT MAX(r.released_on) FROM AppRelease r WHERE r.app_id = a.app_id) AS last_release_on
FROM StoreApp a
WHERE NOT EXISTS (SELECT 1 FROM AppRelease r WHERE r.app_id = a.app_id AND r.released_on >= '2023-07-01')
AND EXISTS (SELECT 1 FROM AppRelease r WHERE r.app_id = a.app_id)
ORDER BY last_release_on, a.app_name← Apple: Trade-In Credit Paid by Device Family · Apple: Apple Music Family Plans With Spare Seats →