Game Play Analysis IV
Task: Report the fraction of players that logged in again on the day after the day they first logged in.
Table: Activity
+--------------+------+
| Column Name | Type |
+--------------+------+
| player_id | INT |
| device_id | INT |
| event_date | date |
| games_played | INT |
+--------------+------+Input: Activity table:
+-----------+-----------+------------+--------------+
| 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 | 2016-03-03 | 5 |
+-----------+-----------+------------+--------------+Output:
+----------+
| fraction |
+----------+
| 0.67 |
+----------+View Reference Solution & Explanation ▼
This solution uses window functions to perform calculations across a set of rows related to the current row, a `CASE` statement for conditional logic, the `ROUND()` function to format numerical output, aggregate functions to compute a single value from the data, a subquery as a derived table or inline value, and date functions to handle time-based filtering or grouping.
SELECT ROUND(SUM(CASE WHEN DATE(event_date, '-1 day') = first_login THEN 1 ELSE 0 END) * 1.0 / COUNT(DISTINCT player_id), 2) AS fraction FROM (SELECT player_id, event_date, MIN(event_date) OVER(PARTITION BY player_id) as first_login FROM Activity);