SQL 70) 查询每个日期新用户的次日留存率
牛客每个人最近的登录日期(五)
http://www.nowcoder.com/questionTerminal/ea0c56cd700344b590182aad03cc61b8
发现其他大神的一个很好懂的解法！
- 求出新用户第一天登录日期
SELECT user_id,MIN(date) AS date FROM login GROUP BY user_id
- 算出有新用户登录的日期的新用户留存率
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 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;
补充其他大神的CASE WHEN 解法
select date
,ifnull(round((sum(case when (user_id,date)in
(select user_id,date_add(date,interval -1 day)
from login group by user_id)
then 1 else 0 end))/
(sum(case when (user_id,date)in
(select user_id,min(date)from login group by user_id)
then 1 else 0 end)),3),0)as p
from login
group by date
order by date;SQL 文章被收录于专栏
SQL
