Game Play Analysis IV
Epic Games
Problem Statement
Epic Games wants to calculate the Day 1 Retention Rate. This is the fraction of players that logged in again on the day immediately after the day they first logged in, rounded to 2 decimal places.
In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.
Rules:
- The result column should be named fraction.
- The result should be rounded to 2 decimal places.
- A player's first login is the record with the earliest event_date for that player_id.
Tests your understanding of
Basic SQL, Aggregation, Subqueries and Date Arithmetic
Input Tables
activity
| player_id(INTEGER) | device_id(INTEGER) | event_date(DATE) | games_played(INTEGER) |
|---|---|---|---|
| 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 |
| 1 | 2 | 2016-03-05 | 10 |
| 4 | 5 | 2020-01-01 | 2 |
| 4 | 5 | 2020-01-02 | 3 |
| 5 | 6 | 2020-05-10 | 1 |
| 5 | 6 | 2020-05-12 | 1 |
| 6 | 7 | 2020-06-01 | 1 |
| 6 | 7 | 2020-06-02 | 4 |
Expected Output
| fraction(DECIMAL) |
|---|
| 0.5 |
Tags
MediumBasic SQLAggregationSubqueriesDate Arithmetic
20-25 min
35%