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
| Column | Type |
|---|---|
| developer_id | int |
| team_name | varchar |
| country | varchar |
Primary key: developer_id.
One row per enrolled developer team.
Table: AppSubmission
| Column | Type |
|---|---|
| submission_id | int |
| developer_id | int |
| app_name | varchar |
| status | enum(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_id | team_name | country |
|---|---|---|
| 1 | Lotus Labs | India |
| 2 | Banyan Apps | India |
| 3 | Indigo Studio | Japan |
| 4 | Peacock Games | UK |
| 5 | Saffron Soft | US |
AppSubmission
| submission_id | developer_id | app_name | status |
|---|---|---|---|
| 11 | 1 | Pocket Tabla | rejected |
| 12 | 1 | Pocket Tabla | approved |
| 13 | 2 | Kirana Ledger | rejected |
| 14 | 2 | Kirana Ledger | in_review |
| 15 | 3 | Raga Tuner | approved |
| 16 | 5 | Yoga Streak | rejected |
Output
| developer_id | team_name |
|---|---|
| 2 | Banyan Apps |
| 4 | Peacock Games |
| 5 | Saffron 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 NULLAnother 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 →