# 思路1:用户第一次登陆表和全量历史登陆表做连接,不是第一次登陆的记录都显示None
# 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即第一次登陆,标记为1,否则为0,然后对日期分组聚合,得出每天第一次登陆用户数目
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