留存率计算

---用户注册日期表
drop table if exists ads_register;
create table ads_register(
    id string,
    rg_ts string
)
;

insert overwrite table ads_register
select 1,'2024-04-29 19:28:00' union all
select 2,'2024-05-30 19:25:00' union all
select 3,'2024-08-03 21:05:00' 
;

--用户访问日期表
drop table if exists ads_activity;
create table ads_activity(
    id string,
    ts string
)
;

insert overwrite table ads_activity
select 1,'2024-05-01 19:28:00' union all
select 1,'2024-05-02 19:53:00' union all
select 1,'2024-05-03 22:00:00' union all
select 1,'2024-05-05 20:55:00' union all
select 1,'2024-05-06 21:58:00' union all
select 2,'2024-06-01 19:25:00' union all
select 2,'2024-06-02 21:00:00' union all
select 2,'2024-06-04 22:05:00' union all
select 2,'2024-06-05 20:59:00' union all
select 2,'2024-06-06 19:05:00' union all
select 3,'2024-08-04 21:05:00' union all
select 3,'2024-08-05 19:10:00' union all
select 3,'2024-08-06 19:55:00' union all
select 3,'2024-08-07 21:05:00' 
;

----留存率
select 
 count(distinct a.id) as tot_id
,count(distinct case when datediff(get_date(b.ts),get_date(a.rg_ts))  = 1 then a.id end)*1.0 / count(distinct a.id) as 1d_retent_rate
,count(distinct case when datediff(get_date(b.ts),get_date(a.rg_ts))  = 2 then a.id end)*1.0 / count(distinct a.id) as 2d_retent_rate
,count(distinct case when datediff(get_date(b.ts),get_date(a.rg_ts))  = 3 then a.id end)*1.0 / count(distinct a.id) as 3d_retent_rate
,count(distinct case when datediff(get_date(b.ts),get_date(a.rg_ts))  = 4 then a.id end)*1.0 / count(distinct a.id) as 4d_retent_rate
from ads_register a
left join ads_activity b
on a.id = b.id
where b.ts > a.rg_ts