留存信息统计

需求：

看某一段时间内，注册的用户7日内留存，比如输入一个时间区间，显示这7日内注册用户总数，然后从第1天到第7天每一天的留存数据。

## ==================================================
##    让读书成为一种生活方式。就像吃喝拉撒每天必须要干的事，
## 终有一天你的举止、言谈、气质会不一样。 
##                                        —- 5sdba 
##
## Created Date: Tuesday, 2021-03-03, 10:54:59 am
## copyright (c):    SZWW Tech. LTD. 
## Engineer:   async 
## Module Name:   
## Revision:   v0.01 
## Description:
##   
## Revision History : 
## Revision  editor date         Description         
## v0.01  async  2021-03-02 File Created
## ==================================================
-- sum_cnt 当日查寻当月注册人数
-- d0_14 最近14天总注册人数
-- d0 当天注册人数
-- d1 第一天注册人数
-- d2 第二天注册人数
 
SELECT register_date,count(1) as sum_cnt,sum(case when day_diff between 0 and 14 then 1 else 0 end) as d0_d14,
       sum(case when day_diff = 0 then 1 else 0 end) day_0,
       sum(case when day_diff = 1 then 1 else 0 end) day_1,
       sum(case when day_diff = 2 then 1 else 0 end) day_2,
       sum(case when day_diff = 3 then 1 else 0 end) day_3,
       sum(case when day_diff = 4 then 1 else 0 end) day_4,
       sum(case when day_diff = 5 then 1 else 0 end) day_5,
       sum(case when day_diff = 6 then 1 else 0 end) day_6,
       sum(case when day_diff = 7 then 1 else 0 end) day_7,
       sum(case when day_diff = 8 then 1 else 0 end) day_8,
       sum(case when day_diff = 9 then 1 else 0 end) day_9,
       sum(case when day_diff = 10 then 1 else 0 end) day_10,
       sum(case when day_diff = 11 then 1 else 0 end) day_11,
       sum(case when day_diff = 12 then 1 else 0 end) day_12,
       sum(case when day_diff = 13 then 1 else 0 end) day_13,
       sum(case when day_diff = 14 then 1 else 0 end) day_14
 from
      (select d.userid,
        date_format(ma.create_time,'%Y-%m-%d') as register_date,
       DATEDIFF(from_unixtime(d.CreateTime,'%Y-%m-%d'),date_format(ma.create_time,'%Y-%m-%d')) as day_diff
         from xxx.xxx ma
join xxx.xx o on ma.id=o.user_id
         left join xx.xx d on ma.id=d.UserId
        where extract(year_month from ma.create_time)={{month}}
     and ma.account_role<50 [[ and ma.service_area={{area}}]]
 
    group by 1,2,3
        ) xa
group by 1
order by 1 