Solution using first_value and lead window function
IntuitionGoal:  To check how many users returned to the app exactly one day after their first activity, divided by the total number of users.Key metrics:Active
# Intuition
**Goal:**  To check how many users returned to the app exactly one day after their first activity, divided by the total number of users.

**Key metrics:**

Active Users: Players who return to the app the next day.
Total Users: The distinct number of players in the dataset.
Logical breakdown:

**Use window functions to determine:**
Each player's first activity date (first_value(event_date)).
The next activity date for each player (lead(event_date)).
Identify users whose next activity date is exactly 1 day after their first activity date.
Count these users as active users.
Divide this count by the total number of distinct users.


# Code
```postgresql []
with cte as 
(select count(player_id) as active_user from 
(select * ,
first_value(event_date) over (partition by player_id order by event_date) as first_value,
lead(event_date) over (partition by player_id order by event_date) as leading
from activity) as x 
where first_value + interval '1 day' = x.leading),

cte2 as 
(select count(distinct player_id) as total_user from activity)

select ROUND(cte.active_user::numeric/cte2.total_user::numeric,2) as fraction from cte,cte2;
```
['PostgreSQL']