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
| Column | Type |
|---|---|
| app_id | int |
| app_name | varchar |
Primary key: app_id.
One app; app_name is unique.
Table: PlayEvent
| Column | Type |
|---|---|
| event_id | int |
| device_id | int |
| app_id | int |
| event_type | enum(install, uninstall) |
| event_at | datetime |
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_id | app_name |
|---|---|
| 1 | Metro Buddy |
| 2 | Raga Tuner |
| 3 | Exam Countdown |
PlayEvent
| event_id | device_id | app_id | event_type | event_at |
|---|---|---|---|---|
| 1 | 1 | 1 | install | 2025-05-01 10:00:00 |
| 7 | 1 | 1 | uninstall | 2025-05-08 10:00:00 |
| 9 | 1 | 1 | install | 2025-05-20 09:00:00 |
| 2 | 2 | 1 | install | 2025-05-02 12:00:00 |
| 8 | 2 | 1 | uninstall | 2025-05-09 12:00:01 |
| 3 | 1 | 2 | install | 2025-05-03 08:00:00 |
| 4 | 1 | 2 | uninstall | 2025-05-03 08:05:00 |
| 5 | 3 | 2 | install | 2025-05-05 21:30:00 |
| 6 | 3 | 2 | uninstall | 2025-05-06 07:00:00 |
Output
| app_name | installs | quick_uninstalls |
|---|---|---|
| Raga Tuner | 2 | 2 |
| Metro Buddy | 3 | 1 |
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_nameAnother 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 →