Google: Published Play Apps With No Installs — SQL Problem
The Play Console team wants to nudge developers whose live listings have never been installed, so they can improve the store listing or screenshots.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #674
Problem statement
The Play Console team wants to nudge developers whose live listings have never been installed, so they can improve the store listing or screenshots.
Return app_id and app_name of every app whose status is published and that has no row at all in Install. Draft and removed apps are never in the answer, even without installs. Return the rows in any order.
A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.
Tables
Table: PlayApp
| Column | Type |
|---|---|
| app_id | int |
| app_name | varchar |
| developer | varchar |
| status | enum(published, draft, removed) |
Primary key: app_id.
One row per app listing on the store.
Table: Install
| Column | Type |
|---|---|
| install_id | int |
| app_id | int |
| device_id | int |
| installed_on | date |
Primary key: install_id.
One row per install; app_id always names an app.
Examples
Example 1
PlayApp
| app_id | app_name | developer | status |
|---|---|---|---|
| 1 | Chai Timer | Kettle Labs | published |
| 2 | Metro Buddy | Transit Dev | published |
| 3 | Rangoli Maker | Kolam Apps | draft |
| 4 | Raga Tuner | Sur Studio | published |
| 5 | Kirana Ledger | Bahi Tech | removed |
Install
| install_id | app_id | device_id | installed_on |
|---|---|---|---|
| 101 | 2 | 9001 | 2025-04-02 |
| 102 | 2 | 9002 | 2025-04-03 |
| 103 | 5 | 9003 | 2025-03-15 |
Output
| app_id | app_name |
|---|---|
| 1 | Chai Timer |
| 4 | Raga Tuner |
How to solve Google: Published Play Apps With No Installs
This is an anti join: keep the apps that have no partner row in Install. The LEFT JOIN form pairs each app with all of its installs and, for an app with none, produces one row whose Install columns are NULL. Testing i.install_id IS NULL — the primary key, which is never NULL in a real row — keeps exactly those apps. An app with several installs produces several joined rows, none of them NULL, so it disappears entirely.
The status test goes in WHERE, because it filters apps, not installs. Draft and removed apps drop out whether or not anyone installed them.
NOT EXISTS states the same thing directly and stops at the first install it finds. NOT IN works here because Install.app_id is never NULL; if it could be, a single NULL would make NOT IN return no rows at all, which is why NOT EXISTS is the safer habit. All three run in one pass with an index on Install.app_id.
Reference solution (MySQL)
SELECT a.app_id, a.app_name
FROM PlayApp a
LEFT JOIN Install i ON i.app_id = a.app_id
WHERE a.status = 'published'
AND i.install_id IS NULLAnother way
SELECT app_id, app_name FROM PlayApp a WHERE status = 'published' AND NOT EXISTS (SELECT 1 FROM Install i WHERE i.app_id = a.app_id)Another way
SELECT app_id, app_name FROM PlayApp WHERE status = 'published' AND app_id NOT IN (SELECT app_id FROM Install)← Google: Zero-Result Searches per Locale · Google: Accounts Over Their Storage Quota →