--用户注册, 注册即新增
create table s_register (user_id bigint, reg_date date);
--用户登录
create table s_login (user_id bigint, login_date date);

--隔日调度运行, 每日的增量统计, 即 1d 统计, 结果表为 dws_user_1d
insert overwrite table dws_user_1d partition (ds = '${bizdate}')
select user_id, 
       min(reg_date) as reg_date 
  from s_register_delta --注册的日增量表
 where ds = '${bizdate}' --日期分区
 group by user_id
 
 --隔日调度运行, 历史累计统计, 即 td 统计, 结果表为 dws_user_td
insert overwrite table dws_user_td partition (ds = '${bizdate}')
select user_id, 
       min(reg_date) as reg_date 
  from dws_user_1d --不限定分区, 此处可以通过增量累计实现, 当前写法为示例
 group by user_id

--隔日调度运行, 每日的增量统计, 即 1d 统计, 结果表为 dws_user_1d(先不考虑两个行为的合并插入同一个表的问题)
insert overwrite table dws_user_1d partition (ds = '${bizdate}')
select user_id, 
       count(login_date) as login_cnt --登录次数
  from s_login_delta --登录的日增量表
 where ds = '${bizdate}' --日期分区
 group by user_id

--延迟7日调度运行, 每日的增量统计, 即 1d 统计, 结果表为 dws_all_1d
insert overwrite table dws_all_1d partition (ds = '${yyyymmdd - 7}') --yyyymmdd-7 表示T-7
select round(remain_cnt / reg_cnt * 100, 2) --7日留存率
  from (select count(*) as reg_cnt, --注册(新增)人数
               count(case when reg_diff = 7 and login_cnt > 0 then td.user_id end) as remain_cnt --留存人数
          from (select user_id, 
                       reg_date, 
                       datediff('${bizdate}', reg_date, 'dd') as reg_diff --注册距今天数
                  from dws_user_td 
                 where ds = '${bizdate}'
                ) td
               left outer join
               (select user_id, 
                       login_cnt 
                  from dws_user_1d 
                 where ds = '${bizdate}'
               ) d1
            on td.user_id = d1.user_id
         where reg_date = '${yyyymmdd -7}') t --yyyymmdd-7 表示T-7