数据准备
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' ) ;
输入
输出
with t1 as (
select * ,
row_number( ) over ( partition by player_id order by event_date) rn1
from activity
)
select player_id,
event_date as first_login
from t1
where rn1= 1