# 思路1:新用户登录时间表和登陆表左连接,这一天只有是新用户登录才显示记录,否则就是None
# 1.新用户登录时间表
# select user_id,min(date)
# from login
# group by user_id
# 2.两表连接
# select login.user_id,login.date,t.user_id,t.dt
# from login 
# left join (
#   select user_id,min(date) dt
#   from login
#   group by user_id
# ) as t
# on login.user_id=t.user_id
# and login.date=t.dt
# 3.按照日期分组聚合
# select login.date,count(t.dt) new
# from login 
# left join (
#   select user_id,min(date) dt
#   from login
#   group by user_id
# ) as t
# on login.user_id=t.user_id
# and login.date=t.dt
# group by login.date
# order by login.date
# 思路2:判断每一条记录的登陆用户是否是新用户,是就为1,不是就为0,最后按照日期分组聚合
# 1.给每个用户的登录时间进行排名-- 窗口函数
# select date, row_number() over(partition by user_id order by date) t_rank
# from login
# 2.打标记:排名为1表示这个用户第一次登陆,即为新用户,标记为1。否则为0
# SELECT *
# ,(case when t_rank=1 then 1 else 0 end) sign
# from (
#   select user_id,date,row_number() over(partition by user_id order by date) t_rank
#   from login) as t
# 3.按照日期分组聚合
SELECT date
,sum(case when t_rank=1 then 1 else 0 end) new
from (
  select user_id,date,row_number() over(partition by user_id order by date) t_rank
  from login) as t  
group by date
order by date