计算次日留存率***

1、where in判断用户是否次日登录+ date_add函数

select
round(count(distinct user_id)/(select count(distinct user_id) from login),3) as p
from login
where (user_id, date) in (
    select user_id, date_add(min(date),interval 1 day)
    from login
    group by user_id
)

2、lead窗口函数 +datediff函数 + case when条件判断

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