题解 | #牛客每个人最近的登录日期(四)#

select date
        ,count(distinct 
            case 
                when (user_id,date) in
                        (select user_id,min(date) 
                        from login 
                        group by user_id) then user_id 
                else null
            end)
from login
group by date
order by date;

找到首次登录的用id

            case 
                when (user_id,date) in
                        (select user_id,min(date) 
                        from login 
                        group by user_id) then user_id 
                else null
            end)

对日期进行分类、排序

group by date
order by date;

完整代码如下

select date
        ,count(distinct 
            case 
                when (user_id,date) in
                        (select user_id,min(date) 
                        from login 
                        group by user_id) then user_id 
                else null
            end)
from login
group by date
order by date;