题解 | 新登录用户次日成功的留存率

1. 巧思：在group_concat(date)中找用户最找登录日期的下一天，如果能找到，那就标记为1，否则为0

select round(sum(t)/count(*),3) from 
(select user_id,if(FIND_IN_SET(date_add(min(date),interval 1 day),group_concat(date)),1,0) as t from login
group by user_id) a

2. 使用左连接方法，where子句中添加条件左表日期+1天=右表日期

SELECT ROUND(SUM(IF(id IS NULL, 0, 1)) / COUNT(1), 3)
FROM (SELECT user_id, MIN(date) AS date FROM login GROUP BY user_id) l
LEFT JOIN login r ON l.user_id = r.user_id AND DATE_ADD(l.date, INTERVAL 1 DAY) = r.date
ORDER BY l.user_id, l.date

3. 使用上面提到的两个字段作为判定条件的where in方法

SELECT
ROUND(COUNT(DISTINCT user_id) / (SELECT COUNT(DISTINCT user_id) FROM login), 3)
FROM login
WHERE (user_id, date)
IN
(SELECT user_id, DATE_ADD(MIN(date),INTERVAL 1 DAY) FROM login GROUP BY user_id);

4. 使用nth_value()和first_value()窗口函数，注意窗口函数外面不能再套用count或sum函数

select round(sum(date)/count(*),3) p from
(select distinct user_id,
case when
(nth_value(date,2) over w - first_value(date) over w)=1 then 1 else 0 end
as date
from login l
window w as(
partition by user_id order by date asc rows between unbounded preceding and unbounded following
))a

5. 使用lead()窗口函数(注意使用count(distinct(case when+字段名 end))判断有多少user_id是符合条件的

select round ( count(distinct case when datediff(ld,date) = 1 then user_id else null end)
/count(distinct user_id), 3) p 
from
(SELECT user_id, date, lead(date,1)over(partition by user_id order by date)ld from login )a

6. 使用两表自连接方法（最简便的方法）

select 
round(count(distinct l1.user_id)/count(distinct l.user_id),3) p
from login l
left join login l1
on l.user_id=l1.user_id
and l.date=date_add(l1.date,interval -1 day)