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

# 每个日期 新登录个数


/*SELECT dates.date, IFNULL(new_users.new, 0) AS new
FROM (
    SELECT DISTINCT date
    FROM login
) AS dates
LEFT JOIN (
    SELECT first_login_date, COUNT(*) AS new
    FROM (
        SELECT user_id, MIN(date) AS first_login_date
        FROM login
        GROUP BY user_id
    ) AS first_logins
    GROUP BY first_login_date
) AS new_users
ON dates.date = new_users.first_login_date
ORDER BY dates.date;*/






select a.date,ifnull(b.new,0) new #由于是先统计后表连接所以会有空值
from
(select distinct date
from login) a
left join
(select first_date,count(user_id) new
from (select user_id,min(date) first_date
from login
group by user_id) c
group by first_date) b
on a.date=b.first_date
order by date


/*select a.date, count(b.user_id) new #先连接后统计则空值直接记为0
from (select distinct date from login) a
left join (select user_id, min(date) first_date from login group by user_id) b on a.date=b.first_date 
group by a.date
order by a.date*/