Fraction of Players with Consecutive Logins: The most efficient way
🚀 If this solution helped you understand the problem and save time, please consider giving it a thumbs up! Your encouragement motivates me to deliver better exp
🚀 **If this solution helped you understand the problem and save time, please consider giving it a thumbs up! Your encouragement motivates me to deliver better explanations.** 


# Intuition

We want to calculate the fraction of players who logged in consecutively after their first login. This can be achieved by first identifying each player's first login date and then checking if they logged in the following day.


# Approach
1. **Identify the first login date:**  
   - We use a `WITH` clause to create a temporary result set `first_login` that stores the earliest login date (`MIN(event_date)`) for each player (`player_id`).
   
2. **Find players with consecutive logins:**  
   - We join the `Activity` table with `first_login` on `player_id` and filter for players who logged in exactly one day after their first login using the `DATEDIFF()` function.

3. **Calculate the fraction:**  
   - The fraction is calculated by dividing the count of distinct players who logged in consecutively by the total number of distinct players who have logged in.
   - The result is rounded to two decimal places for presentation.

# Complexity
- **Time complexity:**  
  - Calculating the first login date requires scanning the entire `Activity` table, which takes **$$O(n)$$** time.
  - The join and the `DATEDIFF()` comparison are done in **$$O(n)$$** as well.
  - Overall, the time complexity is **$$O(n)$$**.

- **Space complexity:**  
  - The space complexity is **$$O(n)$$** due to the temporary tables (`first_login` and `consecutive_login`) storing intermediate results.

# Code
```mysql
WITH first_login AS (
    SELECT player_id, MIN(event_date) AS first_login_date
    FROM Activity
    GROUP BY player_id
),
consecutive_login AS (
    SELECT a.player_id
    FROM Activity a
    JOIN first_login f ON a.player_id = f.player_id
    WHERE DATEDIFF(a.event_date, f.first_login_date) = 1
)
SELECT ROUND(COUNT(DISTINCT cl.player_id) * 1.0 / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM consecutive_login cl;

['Brainteaser', 'Database', 'MySQL']