SQL面试题：用户留存率分析

with tt as(

select tu.id,t1.id,date(tu.register_time) reg_date,date(t1.login_time) login_date

desc_rank()over(partition by date(tu.register_time) order by tu.id) daily_reg

desc_rank()over(partition by date(tu.register_time),date(t1.login_time)order by tul.id ) daily_log

from t_user tu

left join t_user_login t1

on tu.id=t1.id

and date(t1.login_time)=date(tu.register_time)+interval 1 day

or date(t1.login_time)=date(tu.register_time)+interval 7 day

or date(t1.login_time)=date(tu.register_time)+interval 30 day

),

tt2 as

(select reg_date,

login_date,

max(daily_reg),max(daily_log)

from tt

group by reg_date,login_date

),

select reg_date,

max(case when login_date=reg_date+interval 1 day then daily_log end)/max(daily_reg)'rr1'

max(case when login_date=reg_date+interval 7 day then daily_log end)/ max(daily_reg) 'rr7'

max(case when login_date=reg_date+interval 7 day then daily_log end)/ max(daily_reg) 'rr30'

from tt2

group by reg_date