思路1

筛选出所有新用户及其首日登录日期，count新用户个数 group by 日期

但这样会导致过滤 新用户登录=0的日期，因为子查询只选出了 有新用户登录的日期

发现无法通过。于是尝试思路2

# 如何判断当天登录的新用户：同一用户，date=min(date)
select
    date,
    count(user_id) as new
    # 统计有多少新用户
from login
where (user_id,date) in (
    select user_id,min(date)
    from login 
    group by user_id
    # 筛选出 所有用户登录的第一天
)
group by date
order by date asc

思路2：

在原表基础上新增一列「是否新用户」,sum(新用户个数) group by 日期

select 
    a.date,
    sum(case when a.is_new ='新' then 1 else 0 end) as new
from
    (
        select
            l2.id,
            l2.user_id,
            l2.date,
            case when l2.date=l1.min_date then '新' else '旧' end as is_new
        from (
                select user_id,min(date) as min_date
                from login 
                group by user_id
    )l1
    right join login l2 on l2.user_id=l1.user_id

)a
group by a.date
order by a.date asc