Game Play Analysis IV
Preview mode. Log in to edit, run, submit, and save progress.
Description
You have an Activity table that logs when players played games. Each player has a first login date (the minimum event_date for that player). Write a SQL query to report the fraction of players who logged in again the day immediately after their first login date. Round the result to 2 decimal places. (player_id, event_date) is the primary key.
Database Schema
Activity
| Column Name | Type | Description |
|---|---|---|
| player_id | INT | Player identifier |
| device_id | INT | Device used |
| event_date | DATE | Date of activity |
| games_played | INT | Number of games played on that date |
Example
Activity
| player_id | device_id | event_date | games_played |
|---|---|---|---|
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
Output
| fraction |
|---|
| 0.33 |
Explanation:
Player 1 logged in on 2016-03-01 (first login) and again on 2016-03-02 (next day). Players 2 and 3 did not log in the day after their first login. So 1 out of 3 = 0.33.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
Activity
| player_id | device_id | event_date | games_played |
|---|---|---|---|
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
Output
| fraction |
|---|
| 0.33 |