首次登录时间在最近7日内的且用户的最近一次登录时间是今天的,根据首次登录时间分组,后组内的count(*)就是对应日的首日登录总人数(新增用户)
insert overwrite table ads_user_retention
select * from ads_user_retention
union
select
'2021-06-14',
login_date_first create_date,
datediff('2021-06-14',login_date_first) retention_day,
sum(if(login_date_last='2021-06-14',1,0)) retention_count,
count(*) new_user_count,
cast(sum(if(login_date_last='2021-06-14',1,0))/count(*)*100 as decimal(16,2)) retention_rate
from dwt_user_topic
where dt='2021-06-14'
and login_date_first>=date_add('2021-06-14',-7)
and login_date_first<'2021-06-14'
group by login_date_first;