# $1097. 游戏玩法分析 V
# https://leetcode-cn.com/problems/game-play-analysis-v/

# SQL架构
Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int);
Truncate table Activity;
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5');
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-02', '6');
insert into Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-01', '0');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5');

# Write your MySQL query statement below
select
    tmp.first_date as install_dt,
    count(distinct tmp.player_id) as installs,
    round(
        sum(
            if(
                date_add(tmp.first_date, interval 1 day) = event_date,
                1,
                0
            )
        ) / count(distinct tmp.player_id),
        2
    ) as Day1_retention
from
    (
        select
            player_id,
            event_date,
            min(event_date) over(partition by player_id) as first_date
        from
            Activity
    ) tmp
group by
    tmp.first_date;

# clean-up
drop table Activity;