题解 | #牛客每个人最近的登录日期(四)#

-- 第一步：新用户临时表

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;


-- 第四步：两个表联结，并设置空值为0
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;

这道题最大的收获，大概是一个从来没有注意过的函数ifnull(data1,data2)。

这个函数的含义就是，如果第一个位置的值为空值，则此处填写位置2的值。