LeetCode--550. 遊戲玩法分析 IV

建表

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-05-02', 6);
insert into activity values(1, 3, '2017-06-25', 1);
insert into activity values(3, 1, '2016-03-02', 0);
insert into activity values(3, 4, '2018-07-03', 5);

思路：

得出所有玩家次日登入時間，看在原資料中是否存在，統計存在的個數，除以總人數。

select
	ROUND(COUNT(DISTINCT player_id)/(select COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
from
    Activity
where
	(player_id,event_date)
	in
	(select player_id, Date(min(event_date)+1) from Activity GROUP BY player_id);