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

--第一步找出用户首次登陆的日期，代码如下：
(SELECT user_id, MIN(date) AS first_login_date 
         FROM login 
         GROUP BY user_id) AS fl
--第二步找出用户次日登陆的日期，代码如下：
LEFT JOIN 
        login l
    ON 
        fl.user_id = l.user_id 
        AND l.date = DATE_ADD(fl.first_login_date, INTERVAL 1 DAY)
--第三步计算留存率，即次日登陆的用户数/首次登陆的用户数，代码如下：
ROUND(
            SUM(CASE WHEN l.date IS NOT NULL THEN 1 ELSE 0 END) 
            / NULLIF(COUNT(DISTINCT fl.user_id), 0), 3
        ) AS retention_rate
--第四步解决按日期分组计算的问题，即：
SELECT 
        fl.first_login_date, 
        ROUND(
            SUM(CASE WHEN l.date IS NOT NULL THEN 1 ELSE 0 END) 
            / NULLIF(COUNT(DISTINCT fl.user_id), 0), 3
        ) AS retention_rate
..........(第一二三步此处省略展示）
 GROUP BY 
        fl.first_login_date
--第五步，解决不满足次日留存的日期且要以0的记录显示left join为空的问题，即：
SELECT 
    distinct login.date AS wz_date,
    COALESCE(pp.retention_rate, 0) AS p
FROM 
    login
LEFT JOIN 
--完整代码连接起来如下：
SELECT 
    distinct login.date AS wz_date,
    COALESCE(pp.retention_rate, 0) AS p
FROM 
    login
LEFT JOIN (
    SELECT 
        fl.first_login_date, 
        ROUND(
            SUM(CASE WHEN l.date IS NOT NULL THEN 1 ELSE 0 END) 
            / NULLIF(COUNT(DISTINCT fl.user_id), 0), 3
        ) AS retention_rate
    FROM 
        (SELECT user_id, MIN(date) AS first_login_date 
         FROM login 
         GROUP BY user_id) AS fl
    LEFT JOIN 
        login l
    ON 
        fl.user_id = l.user_id 
        AND l.date = DATE_ADD(fl.first_login_date, INTERVAL 1 DAY)
    GROUP BY 
        fl.first_login_date
) AS pp
ON 
    login.date = pp.first_login_date;