力扣sql中等篇练习(四)

1 游戏玩法分析IV

1.1 题目内容

1.1.1 基本题目信息

1.1.2 示例输入输出

1.2 示例sql语句

#  首次登录后第二天登录的玩家数量/玩家总数量(去重)
#  注意首日注册指的是表中的最早的一个日期
SELECT 
ROUND((SELECT count(distinct t1.player_id)FROM(SELECT player_id,min(event_date) e1FROM ActivityGROUP BY player_id)t1INNER JOIN Activity a1ON t1.player_id=a1.player_id AND t1.e1+1=a1.event_date)/(SELECT count(distinct player_id) FROM Activity),2
) fraction

1.3 运行截图

2 项目员工III

2.1 题目内容

2.1.1 基本题目信息

2.1.2 示例输入输出

2.2 示例sql语句

# Write your MySQL query statement below
SELECT p1.project_id,p1.employee_id
FROM 
(SELECT p.project_id,e1.*FROM Project pINNER JOIN Employee e1ON p.employee_id=e1.employee_id
) p1
INNER JOIN
(SELECT p.project_id,MAX(e.experience_years) mYearFROM Project pINNER JOIN Employee eON p.employee_id=e.employee_idGROUP BY p.project_id
) p2
ON p1.project_id=p2.project_id AND p1.experience_years=p2.mYear

2.3 运行截图

3 游戏玩法分析III

3.1 题目内容

3.1.1 基本题目信息

3.1.2 示例输入输出

3.2 示例sql语句

# Write your MySQL query statement below
SELECT a2.player_id,a2.event_date,sum(a1.games_played) games_played_so_far 
FROM Activity a1
INNER JOIN Activity a2
ON a1.player_id=a2.player_id AND a1.event_date<=a2.event_date
GROUP BY a2.player_id,a2.event_date

3.3 运行截图