1.写一条 SQL 查询语句获取每位玩家第一次登陆平台的日期
活动表 Activity：
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
表的主键是 (player_id, event_date)。
这张表展示了一些游戏玩家在游戏平台上的行为活动。
每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。
查询结果的格式如下所示：
Activity 表：
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
Result 表：
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1 | 2016-03-01 |
| 2 | 2017-06-25 |
| 3 | 2016-03-02 |
+-----------+-------------+
解题思路：
- 查找玩家第一次登录平台的日期，即最小日期
select player_id,min(event_date) as first_login from Activity
group by player_id
2.请编写一个 SQL 查询，描述每一个玩家首次登陆的设备名称
Table: Activity
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
(player_id, event_date) 是这个表的两个主键
这个表显示的是某些游戏玩家的游戏活动情况
每一行是在某天使用某个设备登出之前登录并玩多个游戏（可能为0）的玩家的记录
查询结果格式在以下示例中：
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
Result table:
+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
+-----------+-----------+
解题思路：
- 本题要注意，where后面跟两个列名，只有这两个列同时在第一个查询中才会输出
select player_id,device_id from Activity
where (player_id,event_date) in (
select player_id,min(event_date) from Activity group by player_id)
3.编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。
Table: Activity
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
（player_id，event_date）是此表的主键。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0 ）。
查询结果格式如下所示：
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 1 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
Result table:
+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
| 1 | 2016-03-01 | 5 |
| 1 | 2016-05-02 | 11 |
| 1 | 2017-06-25 | 12 |
| 3 | 2016-03-02 | 0 |
| 3 | 2018-07-03 | 5 |
+-----------+------------+---------------------+
对于 ID 为 1 的玩家，2016-05-02 共玩了 5+6=11 个游戏，2017-06-25 共玩了 5+6+1=12 个游戏。
对于 ID 为 3 的玩家，2018-07-03 共玩了 0+5=5 个游戏。
请注意，对于每个玩家，我们只关心玩家的登录日期。
解题思路：
- 窗口函数，以玩家分组按时间排序
# 方法一：窗口函数
select player_id,event_date,
sum(games_played) over (partition by player_id order by event_date) as games_played_so_far
from Activity
# 方法二：自联结
select a.player_id,a.event_date,sum(b.games_played) as games_played_so_far
from Activity a,Activity b
where a.player_id=b.player_id
and a.event_date>=b.event_date
group by a.player_id,a.event_date
order by player_id,event_date
4.编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的分数，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。
Table: Activity
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
（player_id，event_date）是此表的主键。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）。
查询结果格式如下所示：
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
Result table:
+-----------+
| fraction |
+-----------+
| 0.33 |
+-----------+
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33
解题思路：
- 题目要求计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数
- 将题目拆分，先找到玩家首次登录的日期，计算首次登录的日期与登录日期的差值，为1则代表连续两天登录，找出差值为1的玩家数，除以总的玩家数
# 找到首次登录的日期
select player_id,min(event_date) as first_date from Activity group by player_id
# 将首次登录的日期作为临时表
select * from Activity a,
(select player_id,min(event_date) as first_date from Activity group by player_id) b
on b.player_id=a.player_id
# 计算日期差,使用datediff函数
select datediff(a.event_date,b.first_date),
(select count(distinct (player_id)) from Activity)
from Activity a,
(select player_id,min(event_date) as first_date from Activity group by player_id) b
on b.player_id=a.player_id
# 过滤出符合的玩家
select sum(case when datediff(a.event_date,b.first_date)=1 then 1 else 0 end),
(select count(distinct (player_id)) from Activity)
from Activity a,
(select player_id,min(event_date) as first_date from Activity group by player_id) b
on b.player_id=a.player_id
# 使用公式，保留两位小数
select round(sum(case when datediff(a.event_date,b.first_date)=1 then 1 else 0 end)/
(select count(distinct (player_id)) from Activity),2)
from Activity a,
(select player_id,min(event_date) as first_date from Activity group by player_id) b
on b.player_id=a.player_id
# 最终
select round(sum(case when datediff(a.event_date,b.first_date)=1 then 1 else 0 end)/
(select count(distinct (player_id)) from Activity),2) as fraction
from Activity a,
(select player_id,min(event_date) as first_date from Activity group by player_id) b
where b.player_id=a.player_id
5.计算次日留存率
我们将玩家的安装日期定义为该玩家的第一个登录日。
我们还将某个日期 X 的第 1 天保留时间定义为安装日期为 X 的玩家的数量，他们在 X 之后的一天重新登录，除以安装日期为 X 的玩家的数量，四舍五入到小数点后两位。
编写一个 SQL 查询，报告每个安装日期、当天安装游戏的玩家数量和第一天的保留时间。
Activity 活动记录表
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
（player_id，event_date）是此表的主键
这张表显示了某些游戏的玩家的活动情况
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）
查询结果格式如下所示：
Activity 表：
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-01 | 0 |
| 3 | 4 | 2016-07-03 | 5 |
+-----------+-----------+------------+--------------+
Result 表：
+------------+----------+----------------+
| 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
解题思路：
- 本题实为计算玩家次日留存率，次日留存率 = 第一天新增用户数在第二天活跃的用户数 / 当天新增用户数
- 输出结果中，install_dt为首次安装时间即首次登录时间，找到最小时间求出即可
- installs为当天的玩家数量，使用distinct避免重复
- Day1_retention为次日留存率，使用时间差值计算出连续两天登录的人数，除以第一天安装游戏的总人数，结果保留两位小数
- 这里注意需要用登陆时间分组
SELECT b.install_date AS install_dt,COUNT(DISTINCT a.player_id) AS installs,
ROUND(
COUNT(DISTINCT CASE WHEN DATEDIFF(event_date,install_date) = 1 THEN a.player_id ELSE NULL END)/
COUNT(DISTINCT a.player_id)
,2) AS Day1_retention
FROM Activity a LEFT JOIN (
SELECT player_id,MIN(event_date) AS install_date FROM `Activity` GROUP BY player_id ) b
ON a.player_id=b.player_id
GROUP BY install_date本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
