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

WITH tmp AS
(
SELECT 
    *,
    row_number() over (partition by user_id order by date) AS rk,
    lead(date,1) over (partition by user_id order by date) AS next_date
FROM login
)

SELECT
    DISTINCT date,
    ROUND(COALESCE(COUNT(CASE WHEN rk=1 AND timestampdiff(day,date,next_date)=1 THEN user_id END)/COUNT(CASE WHEN rk=1 THEN user_id END),0),3) AS p
FROM tmp
GROUP BY date
ORDER BY date