题解 | #找出待召回的流失用户# 小白拆解做法

with tiaojian as (
select
uid,
count(id) as times
from user_login_tb
group by uid
having count(id)>=3
),tiaojian1 as (
select 
uid,
count(distinct login_date) as days
from user_login_tb
group by uid
having datediff((select max(login_date) from user_login_tb),max(login_date))>29
)

select 
t.uid,
t1.days,
t.times
from tiaojian t inner join tiaojian1 t1 on t.uid=t1.uid
order by days desc,times desc