题解 | #牛客每个人最近的登录日期(五)#
牛客每个人最近的登录日期(五)
https://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8
WITH T AS ( SELECT T.* ,CASE WHEN l.`date` IS NOT NULL THEN 1 ELSE 0 END IND FROM ( SELECT * ,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY `date`) RN FROM login ) T left join (SELECT user_id,date FROM login) l on T.user_id = l.user_id and datediff(l.`date`, (CASE WHEN T.RN = 1 THEN T.`date` END)) = 1 ) SELECT T.`date` # ,SUM(CASE WHEN T.RN = 1 THEN 1 ELSE 0 END) TOTAL # ,SUM(CASE WHEN T.IND = 1 THEN 1 ELSE 0 END) LC_CNT ,ROUND(IFNULL(SUM(CASE WHEN T.IND = 1 THEN 1 ELSE 0 END)/SUM(CASE WHEN T.RN = 1 THEN 1 ELSE 0 END),0),3) P FROM T GROUP BY T.`date` ORDER BY T.`date` ;