Google: Published Play Apps With No Installs

Easy
674.

Google: Published Play Apps With No Installs

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

ColumnTypeValues
app_idint
app_namevarchar
developervarchar
statusenumpublished, draft, removed

Primary key: app_id.

One row per app listing on the store.

Table: Install

ColumnType
install_idint
app_idint
device_idint
installed_ondate

Primary key: install_id.

One row per install; app_id always names an app.

Example

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

PlayApp

app_idapp_namedeveloperstatus
1Chai TimerKettle Labspublished
2Metro BuddyTransit Devpublished
3Rangoli MakerKolam Appsdraft
4Raga TunerSur Studiopublished
5Kirana LedgerBahi Techremoved

Install

install_idapp_iddevice_idinstalled_on
101290012025-04-02
102290022025-04-03
103590032025-03-15

Output

app_idapp_name
1Chai Timer
4Raga Tuner
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.

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

ColumnType
app_idint
app_namevarchar
developervarchar
statusenum(published, draft, removed)

Primary key: app_id.

One row per app listing on the store.

Table: Install

ColumnType
install_idint
app_idint
device_idint
installed_ondate

Primary key: install_id.

One row per install; app_id always names an app.

Examples

Example 1

PlayApp

app_idapp_namedeveloperstatus
1Chai TimerKettle Labspublished
2Metro BuddyTransit Devpublished
3Rangoli MakerKolam Appsdraft
4Raga TunerSur Studiopublished
5Kirana LedgerBahi Techremoved

Install

install_idapp_iddevice_idinstalled_on
101290012025-04-02
102290022025-04-03
103590032025-03-15

Output

app_idapp_name
1Chai Timer
4Raga 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 NULL

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