LeetCode刷题-数据库（MySQL）-550. Game Play Analysis IV

最新推荐文章于 2024-04-25 17:04:54 发布

Asa_Wong

最新推荐文章于 2024-04-25 17:04:54 发布

阅读量1k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： MySQL

本文链接：https://blog.youkuaiyun.com/weixin_41723227/article/details/99952617

MySQL 专栏收录该内容

41 篇文章

订阅专栏

这篇博客探讨了LeetCode第550题——Game Play Analysis IV的解决方案。内容包括问题描述、思路分析和SQL查询实现。题目要求计算首次登录后次日再次登录的玩家比例，结果需要四舍五入到小数点后两位。文章详细解释了如何计算这个比例，重点在于找出连续两天登录的玩家并计算其占比。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

550. Game Play Analysis IV

一、题目描述

Table: Activity

Column Name	Type
player_id	int
device_id	int
event_date	date
games_played	int

(player_id, event_date) is the primary key of this table.
This table shows the activity of players of some game.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on some day using some device.

The query result format is in the following example:

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

Only the player with id 1 logged back in after the first day he had logged in so the answer is 1/3 = 0.33

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/game-play-analysis-iv
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

二、思路分析

Write an SQL query that reports the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.
本题要求计算一个比值，分母是所有不同选手的数量，分子是那些首次在设备登录后连续第二天又登录的选手数量。

分母很容易求，难点在于分子的计算。要计算分子，首先要找到每个选手的首次登录日期。然后，将该查询作为一个临时表与原表连接，再筛选出那些登录日期与首次登录日期相差一天的记录，最后计算数量即可。

三、代码实现

SELECT ROUND
	(
		(
			SELECT 
				COUNT(
					IF(DATEDIFF(a1.event_date, a2.min_date) = 1, 1, null)
					)
			FROM
				Activity a1,
				(
					SELECT
						player_id,
						MIN(event_date) AS min_date
					FROM
						Activity
					GROUP BY player_id
				) a2
			WHERE
				a1.player_id = a2.player_id
		) 
		/ COUNT(DISTINCT(player_id))	
, 2) fraction
FROM
	Activity