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

WITH tt AS (
    SELECT 
        user_id,
        date,
        MIN(date) OVER (PARTITION BY user_id) AS first_day
    FROM login
),
ta AS (
    SELECT 
        user_id,
        date,
        first_day,
        IF(date = DATE_ADD(first_day, INTERVAL 1 DAY), 1, 0) AS if_log,
        IF(date = first_day, 1, 0) AS if_first
    FROM tt
),
tb as(
SELECT 
    first_day AS date,
    ROUND(
        IFNULL(
            SUM(if_log) / COUNT(DISTINCT CASE WHEN if_first = 1 THEN user_id END),
            0
        ),
        3
    ) AS p
FROM ta
GROUP BY first_day)
SELECT DISTINCT t1.date AS date,ifnull(p,0.000) AS p
FROM login t1 
LEFT JOIN tb t2
ON t1.date = t2.date
ORDER BY date ASC