牛客SQL练习-69-牛客每个人最近的登录日期(四)

1. 首先得到每个日期里面，用户登录的数目

select l1.date,count(distinct l1.user_id)
from login l1
group by l1.date

2. 再加一个where判断条件就能从这每个日期里面，用户登录的数目取出哪些是新用户

select l1.date,count(distinct l1.user_id)
from login l1
where l1.date =
(select min(date) from login where user_id=l1.user_id)
group by l1.date

或者窗口函数获取登录的次数，最早就是首次登录，然后分组对rank=1的求和即可

select a.date,
sum(case when rank=1 then 1 else 0 end) new
from 
(select date, row_number() over(partition by user_id order by date) rank
from login) a
group by date