思路:
1.子表用于记录每个用户登录日期以及最早登录时间;
2.外表记录每日新增用户数以及第二天还剩下的比例
#用窗口函数实现,此类方法可以记录n日留存率
select new_dt,
round(count(distinct case when
datediff(in_dt, new_dt)=1 or datediff(out_dt, new_dt)=1 then uid else null end)/
count(distinct t1.uid),2) uv_left_rate
from
(select uid, date(in_time) in_dt, date(out_time) out_dt,
min(date(in_time)) over(partition by uid) new_dt
from tb_user_log
) t1
where MONTH(new_dt) = 11
group by new_dt
order by new_dt