Google: Play Installs Removed Within a Week

Medium
686.

Google: Play Installs Removed Within a Week

A Play Store growth analyst tracks quick uninstalls: an install that the same device removes within a week usually means the app disappointed on first use.

An install is a quick uninstall when the next event for the same device and app is an uninstall at most 7 days (604,800 seconds, inclusive) after it. For every app with at least one install, return app_name, installs and quick_uninstalls. Order by quick_uninstalls descending, then app_name ascending.

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

Primary key: app_id.

One app; app_name is unique.

Table: PlayEvent

ColumnTypeValues
event_idint
device_idint
app_idint
event_typeenuminstall, uninstall
event_atdatetime

Primary key: event_id.

For one device and app, events alternate install, uninstall, install, … with distinct times; the sequence may end on an install.

Example

The rows must come back in the order shown.

PlayApp

app_idapp_name
1Metro Buddy
2Raga Tuner
3Exam Countdown

PlayEvent

event_iddevice_idapp_idevent_typeevent_at
111install2025-05-01 10:00:00
711uninstall2025-05-08 10:00:00
911install2025-05-20 09:00:00
221install2025-05-02 12:00:00
821uninstall2025-05-09 12:00:01
312install2025-05-03 08:00:00
412uninstall2025-05-03 08:05:00
532install2025-05-05 21:30:00
632uninstall2025-05-06 07:00:00

Output

app_nameinstallsquick_uninstalls
Raga Tuner22
Metro Buddy31
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: Play Installs Removed Within a Week — SQL Problem

A Play Store growth analyst tracks quick uninstalls: an install that the same device removes within a week usually means the app disappointed on first use.

  • Difficulty: Medium
  • Topics: Window Functions, Dates, Joins
  • Dialect: MySQL
  • Problem: #686

Problem statement

A Play Store growth analyst tracks quick uninstalls: an install that the same device removes within a week usually means the app disappointed on first use.

An install is a quick uninstall when the next event for the same device and app is an uninstall at most 7 days (604,800 seconds, inclusive) after it. For every app with at least one install, return app_name, installs and quick_uninstalls. Order by quick_uninstalls descending, then app_name ascending.

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

Primary key: app_id.

One app; app_name is unique.

Table: PlayEvent

ColumnType
event_idint
device_idint
app_idint
event_typeenum(install, uninstall)
event_atdatetime

Primary key: event_id.

For one device and app, events alternate install, uninstall, install, … with distinct times; the sequence may end on an install.

Examples

Example 1

PlayApp

app_idapp_name
1Metro Buddy
2Raga Tuner
3Exam Countdown

PlayEvent

event_iddevice_idapp_idevent_typeevent_at
111install2025-05-01 10:00:00
711uninstall2025-05-08 10:00:00
911install2025-05-20 09:00:00
221install2025-05-02 12:00:00
821uninstall2025-05-09 12:00:01
312install2025-05-03 08:00:00
412uninstall2025-05-03 08:05:00
532install2025-05-05 21:30:00
632uninstall2025-05-06 07:00:00

Output

app_nameinstallsquick_uninstalls
Raga Tuner22
Metro Buddy31

How to solve Google: Play Installs Removed Within a Week

The question is about pairs of consecutive events of one device–app combination, which is what LEAD gives: partitioned by device_id, app_id and ordered by time, it puts the next event's type and time on each row. An install whose next event is an uninstall within the window is a quick uninstall. An install that is the last event (the app is still on the phone) has NULL next values and is simply not quick.

The window is inclusive at 604,800 seconds. TIMESTAMPDIFF(SECOND, …) measures the exact gap, so an uninstall at the same clock time seven days later counts and one a second later does not — DATEDIFF would compare only dates and get that boundary wrong.

After filtering to install rows, a grouped count gives installs, and a conditional sum the quick ones; apps without installs never appear. The alternative finds, for each install, an uninstall within DATE_ADD(…, INTERVAL 7 DAY) with no event in between — correct but quadratic per device, where the window sorts each partition once.

Reference solution (MySQL)

WITH e AS (
  SELECT app_id, event_type, event_at,
         LEAD(event_type) OVER (PARTITION BY device_id, app_id ORDER BY event_at) AS next_type,
         LEAD(event_at) OVER (PARTITION BY device_id, app_id ORDER BY event_at) AS next_at
  FROM PlayEvent
)
SELECT a.app_name,
       COUNT(*) AS installs,
       SUM(CASE WHEN e.next_type = 'uninstall'
                 AND TIMESTAMPDIFF(SECOND, e.event_at, e.next_at) <= 604800
                THEN 1 ELSE 0 END) AS quick_uninstalls
FROM e
JOIN PlayApp a ON a.app_id = e.app_id
WHERE e.event_type = 'install'
GROUP BY a.app_id, a.app_name
ORDER BY quick_uninstalls DESC, a.app_name

Another way

SELECT a.app_name, COUNT(*) AS installs, SUM(CASE WHEN EXISTS (SELECT 1 FROM PlayEvent u WHERE u.device_id = i.device_id AND u.app_id = i.app_id AND u.event_type = 'uninstall' AND u.event_at > i.event_at AND u.event_at <= DATE_ADD(i.event_at, INTERVAL 7 DAY) AND NOT EXISTS (SELECT 1 FROM PlayEvent x WHERE x.device_id = i.device_id AND x.app_id = i.app_id AND x.event_at > i.event_at AND x.event_at < u.event_at)) THEN 1 ELSE 0 END) AS quick_uninstalls FROM PlayEvent i JOIN PlayApp a ON a.app_id = i.app_id WHERE i.event_type = 'install' GROUP BY a.app_id, a.app_name ORDER BY quick_uninstalls DESC, a.app_name

← Google: Gmail Spam Filter Precision and Recall per Model · Google: Searches Reformulated Within a Minute →