老用户注册用户数取法

遇到一个问题
取每周的老用户注册用户数
老用户为，注册时间小于本周的用户

换一个思路，用本周末的注册用户数-本周新增注册用户数

SELECT a1.Week
  ,a1.City_name
  ,(a1.W_au_cnt - a2.w_new_finish_cnt)           ---老用户当周完单人数
  ,(a1.W_au_cnt - a2.w_new_finish_cnt) / (a3.w_register_pcnt - a4.W_new_user_cnt)  ---老用户活跃占比
FROM (SELECT WEEKOFYEAR(TO_TIMESTAMP(Stats_date,'yyyyMMdd')) AS Week
    ,City_name
    ,MAX(W_finish_user_cnt) AS W_au_cnt     ---当周完单用户数
  FROM Pub_cockpit_db.Dm_wky_meeting_user_act_ws
  WHERE Stats_date >= from_unixtime(unix_timestamp( date_add(now(),-(dayofweek(now())-2))- interval  7 weeks ),'yyyyMMdd')
  AND  City_name in ('全国','重庆市', '杭州市','长春市','广州市','南京市','武汉市','福州市','成都市','长沙市','郑州市','天津市')
  AND  source=1                                ---t3app
  GROUP BY Stats_date
    ,City_name
  ) a1
LEFT JOIN 
  (SELECT b1.City_name              AS City_name
    ,MAX(b1.w_new_finish_cnt) AS w_new_finish_cnt      ---当周完单用户数
    ,b1.Week                  AS Week
  FROM (SELECT City_name
      ,SUM(W_new_finish_cnt) AS W_new_finish_cnt
      ,WEEKOFYEAR(TO_TIMESTAMP(Ds,'yyyyMMdd')) AS Week
      ,Ds
    FROM Pub_cockpit_db.Ads_mob_dri_user_gather_ws
    WHERE Ds >= from_unixtime(unix_timestamp( date_add(now(),-(dayofweek(now())-2))- interval  7 weeks ),'yyyyMMdd')
    AND City_name in ('全国','南京市','杭州市','武汉市','长沙市' ,'广州市','天津市','重庆市','长春市','成都市','郑州市','福州市')
    GROUP BY City_name
      ,Week
      ,Ds
    ) b1
  GROUP BY City_name
    ,Week
  ) a2
ON  a1.Week = a2.Week
AND  a1.City_name = a2.City_name
LEFT JOIN 
  (SELECT  c1.week                AS Week                                   
    ,Case
      When c1.Register_city = '500100' Then '重庆市'
      When c1.Register_city = '330100' Then '杭州市'
      When c1.Register_city = '220100' Then '长春市'
      When c1.Register_city = '440100' Then '广州市'
      When c1.Register_city = '320100' Then '南京市'
      When c1.Register_city = '420100' Then '武汉市'
      When c1.Register_city = '350100' Then '福州市'
      When c1.Register_city = '510100' Then '成都市'
      When c1.Register_city = '430100' Then '长沙市'
      When c1.Register_city = '120100' Then '天津市'
      When c1.Register_city = '410100' Then '郑州市'
      When c1.Register_city = '12345678' Then '全国'
      Else '其他' 
    End     
              AS City_name
    ,MAX(c1.Passenger_nm)       AS w_register_pcnt    
  FROM (SELECT weekofyear(to_timestamp(Stats_date,'yyyyMMdd'))       AS week
      ,Stats_date
      ,Register_city
      ,COUNT(Passenger_uuid)        AS Passenger_nm
    FROM Pub_cockpit_db.Dm_user_basic_char_dt
    WHERE Stats_date >= from_unixtime(unix_timestamp( date_add(now(),-(dayofweek(now())-2))- interval  7 weeks ),'yyyyMMdd')
      AND Register_city   in ('500100' ,'330100' ,'220100' ,'440100' ,'320100' ,'420100' ,'350100','510100' ,'430100' ,'120100' ,'410100')
      AND source = 't3app'
    GROUP BY weekofyear(to_timestamp(Stats_date,'yyyyMMdd'))
      ,Stats_date
      ,Register_city
    UNION ALL
    SELECT weekofyear(to_timestamp(Stats_date,'yyyyMMdd'))       AS week
      ,Stats_date
      ,'12345678'                                           AS Register_city
      ,COUNT(Passenger_uuid)        AS Passenger_nm
    FROM Pub_cockpit_db.Dm_user_basic_char_dt
    WHERE Stats_date >= from_unixtime(unix_timestamp( date_add(now(),-(dayofweek(now())-2))- interval  7 weeks ),'yyyyMMdd')
      AND source = 't3app'    
    GROUP BY weekofyear(to_timestamp(Stats_date,'yyyyMMdd'))
      ,Stats_date
      ,Register_city
    ) c1
  GROUP BY Week
    ,City_name
  ) a3
ON  a1.Week = a3.Week
AND  a1.City_name = a3.City_name 
LEFT JOIN 
  (SELECT b2.City_name              AS City_name
    ,MAX(b2.W_new_user_cnt)  AS W_new_user_cnt
    ,b2.Week                 AS Week
  FROM (SELECT City_name
      ,SUM(W_new_user_cnt) AS W_new_user_cnt
      ,weekofyear(to_timestamp(ds,'yyyyMMdd'))       AS week
      ,Ds
    FROM Pub_cockpit_db.Ads_mob_dri_user_gather_ws
    WHERE Ds >= from_unixtime(unix_timestamp( date_add(now(),-(dayofweek(now())-2))- interval  7 weeks ),'yyyyMMdd')
    AND City_name in ('南京市','杭州市','武汉市','长沙市' ,'广州市','天津市','重庆市','长春市','成都市','郑州市','福州市','全国')
    GROUP BY City_name
      ,Week
      ,Ds
    ) b2
  GROUP BY City_name
    ,Week
  ) a4
ON  a1.Week = a4.Week
AND  a1.City_name = a4.City_name