/*第一次过于复杂
-- 新用户临时表
select user_id, min(date) as m_date
from login
group by user_id;

-- 根据日期，统计人数，得出有新用户登录时的日期及其数量
select m_date, count(distinct user_id) as new
from (select user_id, min(date) as m_date
    from login
    group by user_id) as n_user
group by m_date
order by m_date;

-- 与原有所有日期联结

select DISTINCT date 
from login;

-- 最终结果
select date, ifnull ( new, 0 )
from (
    select m_date, count(distinct user_id) as new
    from (select user_id, min(date) as m_date
        from login
        group by user_id) as n_user
    group by m_date
    order by m_date
    ) as new_user_date
right outer join (select DISTINCT date from login) as total_date
on total_date.date = new_user_date.m_date
order by total_date.date;
*/
-- 第二次作答
select a.date,(case when new is null then 0 else new end) as new
from (select distinct date as date from login) as a
left outer join
(select m_date, count(user_id) as new
from (select user_id, min(date) as m_date from login group by user_id) as a
group by m_date) as b
on a.date = b.m_date
order by a.date;

第一次做的时候想的太复杂了，所以做起来也复杂。

第二次再看就简单很多了。

主要是会使用窗口函数了。