# LeetCode 1097: Game Play Analysis V

## Problem Restatement

We have an `Activity(player_id, device_id, event_date, games_played)` table.

For each first login date, compute the fraction of players who logged in again the very next day.

Return the result rounded to 2 decimal places.

## Edge Cases

- Match the aggregate to the question: use raw `COUNT` or `SUM` for rows/amounts, and `COUNT(DISTINCT ...)` only for unique entities.
- Tie cases should return every qualifying row unless the statement asks for a single row.
- For outer joins, keep filters on the joined table in the `ON` clause when unmatched rows must remain visible.

## Common Pitfalls

- Do not put aggregate filters in `WHERE`; use `HAVING` after `GROUP BY`.
- When the answer needs all tied winners, avoid `LIMIT 1` unless the statement explicitly asks for one row.

## Implementation

```sql
WITH first_logins AS (
    SELECT player_id, MIN(event_date) AS first_date
    FROM Activity
    GROUP BY player_id
),
retained AS (
    SELECT f.first_date AS install_dt,
           COUNT(DISTINCT a.player_id) AS retained_count
    FROM first_logins f
    LEFT JOIN Activity a
        ON f.player_id = a.player_id
        AND a.event_date = DATE_ADD(f.first_date, INTERVAL 1 DAY)
    GROUP BY f.first_date
),
total AS (
    SELECT first_date AS install_dt,
           COUNT(*) AS total_count
    FROM first_logins
    GROUP BY first_date
)
SELECT t.install_dt,
       t.total_count AS installs,
       ROUND(COALESCE(r.retained_count, 0) / t.total_count, 2) AS Day1_retention
FROM total t
LEFT JOIN retained r ON t.install_dt = r.install_dt;
```

## Code Explanation

`first_logins` finds the first login date for each player.

`retained` counts players who logged in on `first_date + 1 day` (using a LEFT JOIN to count only those who came back).

`total` counts new installs per date.

The final query divides retained by total installs per date.