Apple: Developer Teams With No Approved App Yet

Easy
600.

Apple: Developer Teams With No Approved App Yet

App Review's developer-relations team reaches out to enrolled developer teams that have not yet shipped anything on the App Store.

Return the developer_id and team_name of every team that has no submission with status approved — whether its submissions were all rejected or are still in review, or it has never submitted at all. Return the rows in any order.

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: Developer

ColumnType
developer_idint
team_namevarchar
countryvarchar

Primary key: developer_id.

One row per enrolled developer team.

Table: AppSubmission

ColumnTypeValues
submission_idint
developer_idint
app_namevarchar
statusenumapproved, rejected, in_review

Primary key: submission_id.

One row per build sent to App Review; developer_id always names a team.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Developer

developer_idteam_namecountry
1Lotus LabsIndia
2Banyan AppsIndia
3Indigo StudioJapan
4Peacock GamesUK
5Saffron SoftUS

AppSubmission

submission_iddeveloper_idapp_namestatus
111Pocket Tablarejected
121Pocket Tablaapproved
132Kirana Ledgerrejected
142Kirana Ledgerin_review
153Raga Tunerapproved
165Yoga Streakrejected

Output

developer_idteam_name
2Banyan Apps
4Peacock Games
5Saffron Soft
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: Developer Teams With No Approved App Yet

App Review's developer-relations team reaches out to enrolled developer teams that have not yet shipped anything on the App Store.

  • Difficulty: Easy
  • Topics: Joins, Subqueries
  • Dialect: MySQL
  • Problem: #600

Problem statement

App Review's developer-relations team reaches out to enrolled developer teams that have not yet shipped anything on the App Store.

Return the developer_id and team_name of every team that has no submission with status approved — whether its submissions were all rejected or are still in review, or it has never submitted at all. Return the rows in any order.

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: Developer

ColumnType
developer_idint
team_namevarchar
countryvarchar

Primary key: developer_id.

One row per enrolled developer team.

Table: AppSubmission

ColumnType
submission_idint
developer_idint
app_namevarchar
statusenum(approved, rejected, in_review)

Primary key: submission_id.

One row per build sent to App Review; developer_id always names a team.

Examples

Example 1

Developer

developer_idteam_namecountry
1Lotus LabsIndia
2Banyan AppsIndia
3Indigo StudioJapan
4Peacock GamesUK
5Saffron SoftUS

AppSubmission

submission_iddeveloper_idapp_namestatus
111Pocket Tablarejected
121Pocket Tablaapproved
132Kirana Ledgerrejected
142Kirana Ledgerin_review
153Raga Tunerapproved
165Yoga Streakrejected

Output

developer_idteam_name
2Banyan Apps
4Peacock Games
5Saffron Soft

How to solve Apple: Developer Teams With No Approved App Yet

The question is about the absence of a row: a team qualifies when no approved submission exists for it. That is an anti join, and NOT EXISTS states it directly — for each team, look for an approved submission with its id, and keep the team only when the lookup finds nothing. Teams with only rejected or in-review builds fail the lookup, and so do teams that never submitted, which is exactly the set asked for.

The LEFT JOIN form attaches only the approved submissions (the status test sits in ON) and keeps the rows where nothing attached, i.e. s.submission_id IS NULL. Moving the status test to WHERE would be a bug: it would drop the unmatched teams instead of finding them.

NOT IN works here because developer_id is never NULL in the subquery; with a nullable column, a single NULL would make NOT IN return nothing, which is why NOT EXISTS is the safer habit. Each form is one probe per team with an index on (developer_id, status).

Reference solution (MySQL)

SELECT d.developer_id, d.team_name
FROM Developer d
WHERE NOT EXISTS (
  SELECT 1 FROM AppSubmission s
  WHERE s.developer_id = d.developer_id AND s.status = 'approved'
)

Another way

SELECT d.developer_id, d.team_name FROM Developer d LEFT JOIN AppSubmission s ON s.developer_id = d.developer_id AND s.status = 'approved' WHERE s.submission_id IS NULL

Another way

SELECT developer_id, team_name FROM Developer WHERE developer_id NOT IN (SELECT developer_id FROM AppSubmission WHERE status = 'approved')

← Apple: iCloud Accounts Using More Than Their Plan · Apple: AppleCare Claims Filed in a Device's First 30 Days →