前提

今天为 2020-06-14

建表语句

CREATE EXTERNAL TABLE ads_user_retention
(
    `dt`              STRING COMMENT '统计日期',
    `create_date`     STRING COMMENT '用户新增日期',
    `retention_day`   INT COMMENT '截至当前日期留存天数',
    `retention_count` BIGINT COMMENT '留存用户数量',
    `new_user_count`  BIGINT COMMENT '新增用户数量',
    `retention_rate`  DECIMAL(16, 2) COMMENT '留存率'
) COMMENT '用户留存率'

步骤

1. 求出 2020-06-07 - 2020-06-13 期间 每一天新增的用户

   
   image.png

2. 将 2020-06-14 所有登录了的用户求出

3. 左关联 1 2 表，留下左边所有数据
   

   left join
   image.png

4. 补充：
   （1）日期相减

   
   日期相减

完整sql

insert overwrite table ads_user_retention
select * from ads_user_retention
union 
select
        '2020-06-14',
        create_date,
        datediff('2020-06-14',create_date) retention_day,
       count(t2.user_id) retention_count,
       count(t1.user_id) new_user_count,
       cast(count(t2.user_id) /  count(t1.user_id) * 100 as DECIMAL(16, 2)) retention_rate
from
     (
         select
             -- 2020-06-07 - 2020-06-13 期间，所有新增的用户及其注册日期
             user_id,
             dt create_date
-- 按照用户注册的日期，每天一统计，一个用户是一行
         from dwd_user_register_inc
         where dt >= date_sub('2020-06-14', 7)
           and dt < '2020-06-14'
     ) t1
left join


     (-- 将6-14日所有登录的用户全部求出
         select user_id,
                dt login_date
-- 按照用户登录的日期，每天已统计，一行也是一个用户
         from dwd_user_login_inc
         where dt = '2020-06-14'
     ) t2
on t1.user_id = t2.user_id
group by create_date;