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

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` 
;