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

看完本题，最后写出来的SQL比较长，但是都是由小的SQL拼接出来的

1. 获取新用户登陆--MIN(date)

2. 获取留存，即为新用户登陆前一天第二天登录的数量--ADDDATE(MIN(date),INTERVAL 1 DAY)

3. 之前有求过留存率，使用之前的就好(区别为本体是每个时间)-- count(ADDDATE(MIN(date),INTERVAL 1 DAY)) group by date
   / count(min(date)) group by date

4. 求出来新用户的每一天的留存率--利用login表的distinct(date),IFNULL(留存率，0) left join 留存率表

5. 结果即为所求，只不过SQL稍微youd
   模板化

--新用户的每一天的留存率
SELECT
    DISTINCT(te.date),IFNULL(k.number,0)
FROM
    login te
LEFT JOIN
(
        --留存率
    SELECT c.date,ROUND(d.number/c.number,3) AS number
    FROM
    (       
                -- 新用户登陆前一天第二天登录的数量
        SELECT 
        l.date,COUNT(1) AS number
        FROM
        login l
    ,
    (
        SELECT user_id,MIN(date) AS date FROM login 
        GROUP BY user_id
    )a
    WHERE
        l.user_id = a.user_id
    AND
        l.date = ADDDATE(a.date,INTERVAL 1 DAY)
    GROUP BY
        l.date
    )d
    ,
    (
        -- 获取新用户登陆--MIN(date)
    SELECT 
        b.date,COUNT(1) AS number
    FROM
        (
                        -- 获取新用户登陆diyitia
            SELECT user_id,MIN(date) AS date 
            FROM 
                login 
            GROUP BY 
                user_id
        )b
        GROUP BY
            b.date
    )c
    WHERE
        d.date = ADDDATE(c.date,INTERVAL 1 DAY)
)k
ON
    te.date = k.date