题解 | #牛客每个人最近的登录日期(四)#
牛客每个人最近的登录日期(四)
http://www.nowcoder.com/practice/e524dc7450234395aa21c75303a42b0a
左链接
SELECT l.date, COUNT(l1.user_id)
FROM login l LEFT JOIN login l1
ON l.user_id NOT IN (SELECT DISTINCT user_id FROM login l1 WHERE l1.date < l.date) AND l.id = l1.id
GROUP BY date
ORDER BY l.date
聚合函数
select distinct date
,sum(case when (user_id,date) in
(select user_id,min(date)from login group by user_id)
then 1 else 0 end)
from login
group by date
order by date;