力扣511(MySQL)-游戏玩法分析Ⅰ（简单）

题目：

活动表 Activity：

写一条 SQL 查询语句获取每位玩家 第一次登陆平台的日期。

查询结果的格式如下所示：

 

 解题思路：

方法一：使用dense_rank() over(partition by ... order by ...)：将登录时间按用户id进行分组然后升序排序，最后筛选出排序后为1的数据。

 select player_id,event_date as first_login
 from (
     select player_id,event_date,dense_rank() over(partition by player_id order by event_date) as rnk 
     from Activity
     ) as a
where rnk = 1;

方法二：使用聚合函数min()和group by

select player_id,min(event_date) as first_login
from Activity
group by player_id;

 方法三：使用where() in

select player_id,event_date as first_login
from Activity
where (player_id, event_date) in(
    select player_id, min(event_date) as first_login
    from Activity
    group by player_id
);