metabase sql统计留存率

-- 上周注册了100人，上周以前注册(200人)，
-- 本周登录了50人（上周注册的100人里面有50人本周登录），
-- 本周登录了20人（上周之前注册注册的200人里面有80人在上周登录过并且本周登录了20人）
-- 新用户留存率 50/100
-- 老用户留存率 20/80

使用sql统计出新旧留存率:

SELECT CONCAT(ROUND((MA.CNT3/MA.CNT1)*100,2),'%')  AS "新用户留存率",
       CONCAT(ROUND((MA.CNT5/MA.CNT4)*100,2),'%') AS "老用户留存率"
FROM (SELECT
        SUM(CASE WHEN A.CREATE_TIME BETWEEN DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) + 8 DAY) AND DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) + 2 DAY) -- 上周日至上周六
                 THEN 1 ELSE 0 END ) AS CNT1, -- 上周注册数(新用户留存分母),
        SUM(CASE WHEN A.CREATE_TIME < DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) + 8 DAY) -- 上周日
                 THEN 1 ELSE 0 END ) AS CNT2,  -- 上周以前注册数
        SUM(CASE WHEN A.CREATE_TIME BETWEEN DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) + 8 DAY) AND DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) + 2 DAY) -- 上周日至上周六
                  AND EXISTS (SELECT 1 FROM FCOIN.SETTLEDETAILS B 
                               WHERE B.SETTLEDATE BETWEEN DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) +1 DAY) AND DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) - 5 DAY) -- 本周日至本周六
                                 AND B.USERID = A.ID) 
                 THEN 1 ELSE 0 END) AS CNT3,  -- 上周注册的在本周登录(新用户留存分子)
        SUM(CASE WHEN A.CREATE_TIME < DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) + 8 DAY) -- 上周日
                  AND EXISTS (SELECT 1 FROM FCOIN.SETTLEDETAILS B  
                               WHERE B.SETTLEDATE BETWEEN DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) + 8 DAY) AND DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) + 2 DAY) -- 本周日至本周六
                                 AND B.USERID = A.ID)
                 THEN 1 ELSE 0 END) AS CNT4,  -- 上周以前注册，上周登录过，本周也登录过(旧用户留存分母)
        SUM(CASE WHEN A.CREATE_TIME < DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) + 8 DAY) -- 上周日
                  AND EXISTS (SELECT 1 FROM FCOIN.SETTLEDETAILS B  
                               WHERE B.SETTLEDATE BETWEEN DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) + 8 DAY) AND DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) + 2 DAY) -- 上周日至上周六
                                 AND B.USERID = A.ID)
                  AND EXISTS (SELECT 1 FROM FCOIN.SETTLEDETAILS B 
                               WHERE B.SETTLEDATE BETWEEN DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) +1 DAY) AND DATE_SUB({{S_TIME}},INTERVAL WEEKDAY({{S_TIME}}) - 5 DAY) -- 本周日至本周六
                                 AND B.USERID = A.ID) 
                 THEN 1 ELSE 0 END) AS CNT5  -- 上周以前注册，上周登录过，本周也登录过(旧用户留存分子)
   FROM ACCOUNT.USERS A
  WHERE A.NATION<>'86' and A.account_role<50 and service_area<>'cn') MA