题解 | #牛客每个人最近的登录日期(四)#
牛客每个人最近的登录日期(四)
http://www.nowcoder.com/practice/e524dc7450234395aa21c75303a42b0a
/*
SELECT date,COUNT(DISTINCT user_id) AS new
FROM login
WHERE id IN
(SELECT id FROM(
SELECT *,
ROW_NUMBER()over(partition by user_id order by date) as rank_num
from login)t2
WHERE t2.rank_num=1)
GROUP by date
ORDER BY date;
*/
/上面的做法没有把每天为0的统计上; 可以利用if_new打上标签，计算标签和/
SELECT t1.date,sum(t1.if_new) AS new
FROM
(
SELECT t2.*,(case when rank_num=1 then 1 else 0 end) as if_new
FROM(
SELECT *,
ROW_NUMBER()over(partition by user_id order by date) as rank_num
from login)t2
)t1
GROUP by t1.date
ORDER BY t1.date;
投递BOSS直聘等公司6个岗位