SQL实战69.牛客每个人最近的登录日期(四)

题目

解答

select date,sum(case when ranking=1 then 1 else 0 end) as new
from (select date,row_number () over (partition by user_id order by date) as ranking
      from login) as t
group by date
order by date;

select l.date,if(count(distinct a.user_id)is null,0,count(distinct a.user_id)) as new
from login as l
left join(
        select user_id,min(date) as n_date
        from login 
        group by user_id) a
on l.date = a.n_date
group by l.date
order  by l.date;

select distinct l.date,if(b.cnt is not null,b.cnt,0) as cnt
from(
    select a.first_login_date,count(a.user_id) as cnt
    from (
        select user_id,min(date) as first_login_date
        from login 
        group by user_id) a 
    group by a.first_login_date) b
right join login as l
on b.first_login_date = l.date
order by l.date;