匹配条件的计数+保证数据的完整性

题目：查询每个日期登录新用户个数，并且查询结果按照日期升序排序
思路：
既要找到每个日期最新登录的用户，又要保证数据的完整。
这里会涉及到两种途径实现：
第一用窗体函数row_number() partition by user_id order by date)按日期升序排序赋予登录顺序号row_n（能一次达到两个条件），
第二用先min()函数和group by ...筛选出最新登录日期min(date)和用户号user_id;再嵌套子查询达到数据的完整；
欲匹配条件的计数，应该想到sum()+case when...的使用。
途径一：窗体函数row_number()+sum()+case when...

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

途径二：min()函数和group by ...+sum()+case when...

select date,
       sum(case when (user_id,date) in (select user_id,
                                               min(date) as date
                                        from login
                                        group by user_id)
                then 1 else 0 end) as new 
from login
group by date
order by date;