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

-- 使用 CTE 计算每个用户的首次登录日期
WITH first_login AS (
    SELECT 
        user_id,
        MIN(date) AS first_login_date
    FROM 
        login
    GROUP BY 
        user_id
),
-- 使用 CTE 计算每个用户前一天的登录日期
previous_day_login AS (
    SELECT 
        user_id,
        DATE_ADD(date, INTERVAL -1 DAY) AS prev_date
    FROM 
        login
)
-- 主查询计算每日比例
SELECT 
    l.date,
    IFNULL(
        ROUND(
            SUM(
                CASE 
                    WHEN fl.user_id IS NOT NULL AND pdl.user_id IS NOT NULL 
                    THEN 1 
                    ELSE 0 
                END
            ) / SUM(
                CASE 
                    WHEN fl.user_id IS NOT NULL 
                    THEN 1 
                    ELSE 0 
                END
            ), 
            3
        ), 
        0
    ) AS p
FROM 
    login l
-- 左连接首次登录日期
LEFT JOIN first_login fl ON l.user_id = fl.user_id AND l.date = fl.first_login_date
-- 左连接前一天登录日期
LEFT JOIN previous_day_login pdl ON l.user_id = pdl.user_id AND l.date = pdl.prev_date
GROUP BY 
    l.date
ORDER BY 
    l.date;