LeetCode刷题-数据库（MySQL）- 512.Game Play Analysis II

最新推荐文章于 2022-09-21 13:42:38 发布

Asa_Wong

最新推荐文章于 2022-09-21 13:42:38 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL

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

MySQL 专栏收录该内容

41 篇文章

订阅专栏

该博客介绍了如何使用SQL查询解决LeetCode中的512(Game Play Analysis II)问题。作者首先详细解释了题目的背景和数据表结构，接着分享了解题思路，即找出每个玩家的首次登录设备。最后给出了实现这个查询的SQL代码。

512.Game Play Analysis II

一、题目描述

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.

Write a SQL query that reports the device that is first logged in for each player.
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-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

二、思路分析

先找出每个player_id对应的最早登录日期，将其作为一个临时表右连接原表，并通过ON筛选出符合题意的结果。

三、代码实现

SELECT
	a.player_id, a.device_id
FROM
	Activity a
RIGHT JOIN (
		SELECT 
			player_id,
			min(event_date) AS first_date
		FROM
			Activity
		GROUP BY player_id	
	) b
ON
	a.event_date = b.first_date
WHERE
	a.player_id = b.player_id;