牛客网69题

解法1：

select 
    l.date
    ,case when l3.new then l3.new else 0 END
from
(
select
    date
from login
group by date
) as l left join 
(
select
    date
    ,count(1) AS new
from login AS l1
where user_id not in 
    (
        select 
        user_id
        from login AS l2
        where l1.date > l2.date
    )
group by date) AS l3
ON l.date = l3.date

核心是

select
    date
    ,count(1) AS new
from login AS l1
where user_id not in 
    (
        select 
        user_id
        from login AS l2
        where l1.date > l2.date
    )
group by date

但是结果不会显示新增为0 的记录

别人的解法，每个思路都很厉害，值得借鉴：
2、窗口函数

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

3、用groupby 选择新增日期

select login.date,ifnull(n1.new_num,0)
from login 
left join 
(select l1.date,count(distinct l1.user_id) as new_num
from login l1
where l1.date =
(select min(date) from login where user_id=l1.user_id)
group by l1.date) n1
on login.date = n1.date
group by login.date order by login.date

4、最优雅编码

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