【MySQL】LeetCode 511&512

Activity表，主键(player_id, event_date)
+-----------+-----------+------------+--------------+
| 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            |
+-----------+-----------+------------+--------------+

511 游戏玩法分析I

找出每个玩家第一次登录的日期
mysql> select player_id,min(event_date) as first_event_date 
    -> from activity
    -> group by player_id;
+-----------+------------------+
| player_id | first_event_date |
+-----------+------------------+
|         1 | 2016-03-01       |
|         2 | 2017-06-25       |
|         3 | 2016-03-02       |
+-----------+------------------+
3 rows in set (0.00 sec)

512 游戏玩法分析II

找出每个玩家第一次登录的日期
mysql> select player_id,device_id from ( select player_id,device_id,row_number()over(partition by player_id order by event_date ) as rn from activity ) t where rn =1;
+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
|         1 |         2 |
|         2 |         3 |
|         3 |         1 |
+-----------+-----------+
3 rows in set (0.00 sec)