drop table if EXISTS Activity;
create table Activity(
player_id int,
device_id int,
event_date date,
games_played int 
);
insert into Activity values(1, 2, '2016-03-01', 5);
insert into Activity values(1, 2, '2016-03-02', 6);
insert into Activity values(2, 3, '2017-06-25', 1);
insert into Activity values(3, 1, '2016-03-01', 0);
insert into Activity values(3, 4, '2016-07-03', 5);

select a1.install_dt,
       count(1) installs,
       round(count(a2.event_date)/count(*),2) Day1_retention
from(
		-- 算出每个人的安装日期
    select player_id,min(event_date) install_dt
    from Activity
    group by player_id
) a1
left join Activity a2
		-- 根据条件安装日期后一天登录为重新登录
    on a1.player_id = a2.player_id and datediff(a2.event_date,a1.install_dt)=1
group by a1.install_dt