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

WITH first_date AS (
    SELECT f.user_id, MIN(f.date) AS date_1
    FROM login AS f
    GROUP BY f.user_id
),
second_date AS (
    SELECT s.user_id, s.date AS date_2, f.date_1
    FROM login AS s
    RIGHT JOIN first_date AS f
    ON s.user_id = f.user_id
    AND  s.date = DATE_ADD(f.date_1, INTERVAL 1 DAY)
),
total_count AS (
    SELECT l.date, SUM(IF(f.date_1 IS NOT NULL, 1 , 0)) AS first_cnt
    FROM login AS l
    LEFT JOIN first_date AS f
    ON l.date = f.date_1
    AND l.user_id = f.user_id
    GROUP BY l.date
),
second_count AS (
    SELECT COUNT(user_id) AS second_cnt, date_1
    FROM second_date
    GROUP BY date_1
)
SELECT t.date, 
ROUND(IF(second_cnt IS NULL, 0, second_cnt) / IF(first_cnt=0,1,first_cnt), 3) AS p
FROM total_count AS t
LEFT JOIN second_count AS s
ON t.date = s.date_1
ORDER BY t.date ASC;