select user_id, date,lag(date, 1) over (partition by user_id order by date) as lastdate from login;

alt

select `date`, sum(case when lastdate is null then 1 else 0 end) as new 
from 
(select user_id, `date`,lag(`date`, 1) over (partition by user_id order by `date`) as lastdate from login) 
as t 
group by t.`date`;