-
Notifications
You must be signed in to change notification settings - Fork 0
/
DB 512. Game Play Analysis II
78 lines (64 loc) · 3.49 KB
/
DB 512. Game Play Analysis II
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int)
Truncate table Activity
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5')
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-05-02', '6')
insert into Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-02', '0')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5')
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 games.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging
out on someday using some device.
Write an SQL query to report the device that is first logged in for each player.
Return the result table in any order.
The query result format is in the following example.
Example 1:
Input:
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 |
+-----------+-----------+------------+--------------+
Output:
+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
+-----------+-----------+
Answer
select a.player_id, a.device_id
from Activity a left join (select player_id, Min(event_date) as d
from Activity group by player_id) temp on a.player_id = temp.player_id
where a.event_date = temp.d
首先,它执行一个内部查询,该查询选择每个玩家的最早登录日期,并使用 GROUP BY 子句按照玩家分组。
然后,它执行一个外部查询,该查询使用左连接将内部查询的结果与 Activity 表连接在一起。左连接保证了如果一个玩家没有登录记录,
该玩家仍然会在结果表中出现,但是该玩家的 device_id 将为空。
最后,该查询使用 WHERE 子句筛选出每个玩家第一次登录的设备。该查询选择玩家的 player_id 和第一次登录的设备的 device_id。
SELECT player_id
,device_id
FROM
(SELECT player_id
,device_id
,rank() OVER(PARTITION BY player_id ORDER BY event_date) AS rnk
FROM Activity) rnk_table
WHERE rnk_table.rnk = 1
首先,它执行一个内部查询,该查询选择每个玩家的 player_id,device_id 和窗口函数 rank()。窗口函数 rank() 通过 PARTITION BY
子句按照玩家分组,并使用 ORDER BY 子句按照 event_date 升序排列。窗口函数计算每个玩家的登录次序,并将其存储在列 rnk 中。
然后,它执行一个外部查询,该查询使用 WHERE 子句筛选出内部查询的结果表中的每个玩家的第一次登录。最终结果为每个玩家的 player_id
和第一次登录的设备的 device_id。