# 留存率 = 连续两天都登录的用户/总用户
# 连续两天都登录，需要使用窗口函数：
select
   round(count(distinct a.user_id)/(select count(distinct user_id) from login),3) as p
from
(select
    user_id,
    client_id,
    date,
    datediff(date,lag(date,1,date) over(partition by user_id order by date)) as diffs
from
    login) a
where 
    a.diffs = 1

可以采用窗口函数的方法进行计算