sql-游戏玩法分析-复购

–游戏玩法分析
–1建表插入数据
Create table If Not Exists 12_Activity (player_id int, device_id int, event_date DATETIME , games_played int);
Truncate table 12_Activity;
insert into 12_Activity (player_id, device_id, event_date, games_played) values (1, 2, ‘2016-03-01 00:00:00’, 5);
insert into 12_Activity (player_id, device_id, event_date, games_played) values (1, 2, ‘2016-03-02 00:00:00’, 6);
insert into 12_Activity (player_id, device_id, event_date, games_played) values (2, 3, ‘2017-06-25 00:00:00’, 1);
insert into 12_Activity (player_id, device_id, event_date, games_played) values (3, 1, ‘2016-03-01 00:00:00’, 0);
insert into 12_Activity (player_id, device_id, event_date, games_played) values (3, 4, ‘2018-07-03 00:00:00’, 5);
SELECT * from 12_Activity;
–2.1编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。详细情况请查看示例。

–	player_id	event_date	games_played_so_far
–	1	2016-03-01	5
–	1	2016-05-02	11
–	2	2017-06-25	1
–	3	2016-03-02	0
–	3	2018-07-03	5

–2.1.1sql实现
SELECT
player_id,
event_date,
SUM(games_played) over(PARTITION BY player_id ORDER by event_date) games_played_sum
from 12_Activity;
–2.2编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的百分比，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

–	fraction
–	0.33

–2.2.1 方法一： 时间 用户 第二天用户 join做法，但是此题是先求出每个用户首次登陆的时间
-------------：首次登陆后的第二天又登陆的用户数/首次登陆的用户数。 首次登陆的用户数=所有用户数(去重) select count(distinct player_id) from 12_Activity;
-------------：首次登陆后的第二天又登陆的用户数
SELECT
round((SELECT
COUNT(DISTINCT t1.player_id) ct
FROM
(SELECT
player_id,
min(event_date) event_date
from 12_Activity
group BY player_id)t1
join 12_Activity t2
on t1.player_id=t2.player_id
and dateadd(t1.event_date,1,‘dd’)=t2.event_date)/(select count(distinct player_id) from 12_Activity),2) as fraction;
–2.2.1 方法二：
SELECT
ROUND(
(SELECT
COUNT(DISTINCT t3.player_id)
FROM
(SELECT
t1.player_id,
datediff(t2.event_date,t1.event_date) diffdate
from
12_Activity t1
join 12_Activity t2
on t1.player_id=t2.player_id
and t1.event_date<=t2.event_date)t3
where t3.diffdate=1) /(select count(distinct player_id) from 12_Activity),2) fraction;
–2.3：编写一个 SQL 查询，报告每个安装日期、当天安装游戏的玩家数量和第一天的保留时间。

–	install_dt	installs	Day1_retention
–	2016-03-01	2	0.50
–	2017-06-25	1	0.00

– 提示：玩家 1 和 3 在 2016-03-01 安装了游戏，但只有玩家 1 在 2016-03-02 重新登录，所以 2016-03-01 的第一天保留时间是 1/2=0.50
– 玩家 2 在 2017-06-25 安装了游戏，但在 2017-06-26 没有重新登录，因此 2017-06-25 的第一天保留为 0/1=0.00
– 最终SQL:
SELECT
t2.install_dt,
t2.installs,
SUM(if(t3.player_id is not null and t3.event_date is not null,1,0))/t2.installs Day1_retention
FROM
(SELECT
t1.install_dt,
t1.player_id,
count(DISTINCT t1.player_id) OVER(PARTITION by t1.install_dt) installs
from(
SELECT
player_id,
min(event_date) install_dt
from 12_Activity
group by player_id) t1) t2
left join 12_Activity t3
on t2.player_id=t3.player_id
and dateadd(t2.install_dt,1,‘dd’)=t3.event_date
GROUP by
t2.install_dt,
t2.installs;