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

SELECT a.date,ROUND(
    COUNT(DISTINCT login.user_id)/ COUNT(a.user_id)
    ,3) AS p
FROM (SELECT user_id,MIN(date) AS date 
    FROM login 
    GROUP BY user_id) AS a
LEFT JOIN login
ON login.user_id=a.user_id
AND login.date=DATE_ADD(a.date,INTERVAL 1 DAY)
GROUP BY a.date
UNION
SELECT date,0.000 AS p
FROM login
WHERE date NOT IN(
    SELECT MIN(date) 
    FROM login 
    GROUP BY user_id)
ORDER BY date;

1、求新用户第一天的登录日期

SELECT user_id,MIN(date) AS date 
    FROM login 
    GROUP BY user_id

2、新用户第二天留存

LEFT JOIN login
ON login.user_id=a.user_id
AND login.date=DATE_ADD(a.date,INTERVAL 1 DAY)

3、UNION补上没有新用户登录的日期

UNION
SELECT date,0.000 AS p
FROM login
WHERE date NOT IN(
    SELECT MIN(date) 
    FROM login 
    GROUP BY user_id)
ORDER BY date;

完整代码如下：

SELECT a.date,ROUND(
    COUNT(DISTINCT login.user_id)/ COUNT(a.user_id)
    ,3) AS p
FROM (SELECT user_id,MIN(date) AS date 
    FROM login 
    GROUP BY user_id) AS a
LEFT JOIN login
ON login.user_id=a.user_id
AND login.date=DATE_ADD(a.date,INTERVAL 1 DAY)
GROUP BY a.date
UNION
SELECT date,0.000 AS p
FROM login
WHERE date NOT IN(
    SELECT MIN(date) 
    FROM login 
    GROUP BY user_id)
ORDER BY date;