SQL70 牛客留存率

---

一、题目

牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。
有一个登录(login)记录表，简况如下:

请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:

二、解题

1. 先找出有新用户登录的日期的留存率
   （1）找出新用户登录的日期
   （2）这些新用户第二天仍然登录的次数
   （3）计算留存率（去重）
   但是当新用户第二天登录为0时，结果不显示
   将两个表左连接。
   2.当无新用户新增的日期，直接设置留存率为0
   3.将两个临时表进行连接

SELECT t.firstdate date,ROUND(
     count(distinct l2.user_id)/count(t.user_id),3) p
FROM 
(
SELECT user_id,min(date) as firstdate
FROM login
GROUP BY  user_id
 ) t
LEFT JOIN
login l2
ON t.user_id=l2.user_id
AND DATE_ADD(t.firstdate,INTERVAL 1 DAY)=l2.date
GROUP BY t.firstdate
#UNION没有新用户登录的日期
UNION
SELECT date,0 as p
FROM login
WHERE date not IN
(SELECT min(date)
 FROM login
 GROUP BY user_id
)
ORDER BY date;

2. IFNULL

关键：

ifnull(round(count(distinct l2.user_id)/count(distinct t.user_id),3),0) p

SELECT t2.date,
       ifnull(round(count(distinct l2.user_id)/count(distinct t.user_id),3),0) p
FROM   #该日期的新用户
    (
        SELECT date,user_id
        FROM login l1
        WHERE (user_id,date) IN
            #找到用户及其最早登录日期
            (SELECT user_id,min(date)
             FROM login
             GROUP BY user_id
            )
    ) t
LEFT JOIN login l2  #次日仍留存的用户
ON t.user_id=l2.user_id
AND l2.date=DATE_ADD(t.date,INTERVAL 1 DAY)
#所有时间
RIGHT JOIN 
(SELECT distinct(date) FROM login) as t2
ON t2.date=t.date
GROUP BY t2.date
ORDER BY t2.date ;