最近练习了大厂的题目,发现其实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