最近练习了大厂的题目,发现其实sql语句总量并不大,解题重点在于逻辑一定要清晰。一下有几个注意点 1:先将跨天的日期表生成,这种简单的行相加就用union函数就可以,其中union all不去重 而union则会自动过滤重复的行 2:如何判断新客户,可以理解以前没有出现过,所以可以统计累计出现次数,之前的题目可以学习到 sum+ over(order by)可以求累计和 3:一般看留存率的题目都可以使用左连接,这样第二天的数据如果缺少就会是null值,非常的常用 4: 一个常用技巧,使用

with xxxx as  (select xxxx)

语句可以保存临时select语句,反复运用某个临时表时可以加快速度,节省大量代码长度

with base as 
(select uid,dt,count(*) over(partition by uid order by dt) as times
from 
(select uid , left(in_time,10) as dt
from tb_user_log
UNION
select uid , left(out_time,10) as dt
from tb_user_log
order by dt,uid)   tmp
order by dt,uid)

select today.dt ,
       round(count(tommorw.dt)/ count(*),2)  
 from 
 base today left join base tommorw
 on today.uid=tommorw.uid and tommorw.dt=TIMESTAMPADD(day,1,today.dt)
 where today.times=1 and today.dt like '2021-11%'
 group by dt
 order by dt