Apple: App Store Apps Not Updated Since Mid-2023

Easy
603.

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.

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

ColumnType
app_idint
app_namevarchar
developervarchar

Primary key: app_id.

One row per app on the store; names are unique.

Table: AppRelease

ColumnType
app_idint
versionvarchar
released_ondate

Primary key (together): app_id, version.

One row per version an app shipped.

Example

The rows must come back in the order shown.

StoreApp

app_idapp_namedeveloper
1Pocket TablaLotus Labs
2Metro MapperBanyan Apps
3Raga TunerIndigo Studio
4Tiffin PlannerNeem Tech

AppRelease

app_idversionreleased_on
11.02022-03-10
11.12023-06-30
22.02022-11-01
22.12023-07-01
31.02021-08-15
43.42024-02-02

Output

app_namelast_release_on
Raga Tuner2021-08-15
Pocket Tabla2023-06-30
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: 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

ColumnType
app_idint
app_namevarchar
developervarchar

Primary key: app_id.

One row per app on the store; names are unique.

Table: AppRelease

ColumnType
app_idint
versionvarchar
released_ondate

Primary key: app_id, version.

One row per version an app shipped.

Examples

Example 1

StoreApp

app_idapp_namedeveloper
1Pocket TablaLotus Labs
2Metro MapperBanyan Apps
3Raga TunerIndigo Studio
4Tiffin PlannerNeem Tech

AppRelease

app_idversionreleased_on
11.02022-03-10
11.12023-06-30
22.02022-11-01
22.12023-07-01
31.02021-08-15
43.42024-02-02

Output

app_namelast_release_on
Raga Tuner2021-08-15
Pocket Tabla2023-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_name

Another 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 →