sql留存率简单的小应用

—全量表2021-01-01的数据
—求不同app_id在 2021-01-01的用户次留和7留
select
‘2021-01-01’
,app_id
,round(sum(second)/count(),2) second_keep
,round(sum(seven)/count(),2) seven_keep
from
(
select
user_id
,app_id
,sum( case when diff = 1 then 1 else 0 end) as second
,sum( case when diff = 7 then 1 else 0 end) as seven
from
(
select
user_id
,app_id
,LOGIN_TIME
,(LOGIN_TIME - to_date(‘2021-01-01’,‘yyyy-mm-dd’)) as diff
from user_active_ds
) a
group by
user_id,app_id)
group by app_id