新用户次日留存率=次日连续登陆用户数/总的新用户数

timestampdiff(day,date1,date2) 计算方式为date2-datet1的差值

datediff(date1,date2)计算方式为date1-date2的差值

SELECT
round(count(DISTINCT b.user_id)/(
select count(DISTINCT c.user_id) from login c),3)
from
(SELECT
a.*,
LAG(date) over(partition by user_id order by date) d
from login a #按照user_id分组，日期升序向上偏移得到该user_id前一次登陆日期，再用datediff(date1,date2)计算方式为date1-date2的差值为1即选出连续登陆的用户
)b
where timestampdiff(DAY, b.d,b.date)=1