WITH ODS_BASE_01 AS (  --运营人员
    SELECT A.USER_ID,
           CASE WHEN MAX(CASE WHEN A.DATA_TYPE=1 THEN A.USER_NAME ELSE NULL END) IS NOT NULL
                    THEN MAX(CASE WHEN A.DATA_TYPE=1 THEN A.USER_NAME ELSE NULL END)
                ELSE MAX(CASE WHEN A.DATA_TYPE=2 THEN A.USER_NAME ELSE NULL END)  END AS USER_NAME,
           CASE WHEN MAX(CASE WHEN A.DATA_TYPE=1 THEN A.WORK_NAME ELSE NULL END) IS NOT NULL
                    THEN MAX(CASE WHEN A.DATA_TYPE=1 THEN A.WORK_NAME ELSE NULL END)
                ELSE MAX(CASE WHEN A.DATA_TYPE=2 THEN A.WORK_NAME ELSE NULL END) END AS  WORK_NAME
    FROM ODS_EXCEL_QYWX_ZONGBU A
    WHERE A.TYPE_ID='01'
    GROUP BY A.USER_ID 
)
,
ODS_BASE_02 AS (  --按天 平均首次回复时长
         SELECT DATE_FORMAT(A.STAT_DATE, '%Y%m%d') AS STATIS_DATE,
                A.USER_ID,A.AVG_REPLY_TIME
         FROM QYWX_USER_BEHAVIOR_DATA A
                  INNER JOIN (SELECT DISTINCT USER_ID
                              FROM  ODS_EXCEL_QYWX_ZONGBU ) B ON A.USER_ID = B.USER_ID
         WHERE A.STAT_DATE >=DATE'2021-01-01'
           AND  DATE_FORMAT(A.STAT_DATE, '%Y%m') >= '202401'
           AND   AVG_REPLY_TIME IS NOT NULL 
)
,
ODS_BASE_03 AS (  -- 按天 当天拉黑企微好友人数
        SELECT A.USER_ID,
                                        CASE WHEN A.USER_BLOCKING_TIME IS NULL
                                                        THEN DATE_FORMAT(A.EXTERNAL_BLOCKING_TIME, '%Y%m%d')
                                                        WHEN A.USER_BLOCKING_TIME IS NOT NULL AND A.USER_BLOCKING_TIME < A.EXTERNAL_BLOCKING_TIME
                                                        THEN DATE_FORMAT(A.USER_BLOCKING_TIME, '%Y%m%d')
                                                        ELSE DATE_FORMAT(A.EXTERNAL_BLOCKING_TIME, '%Y%m%d') END   AS STATIS_DATE,
                                 COUNT(DISTINCT  A.EXTERNAL_USER_ID) AS QW_NUM_QXGZ
        FROM QYWX_USER_CONTACT_RELATION A
        WHERE A.EXTERNAL_BLOCKING_TIME  >=DATE'2021-01-01'
         AND  DATE_FORMAT(A.EXTERNAL_BLOCKING_TIME, '%Y%m') >='202401' --时间参数
        GROUP BY   A.USER_ID,
         CASE WHEN A.USER_BLOCKING_TIME IS NULL
                                                        THEN DATE_FORMAT(A.EXTERNAL_BLOCKING_TIME, '%Y%m%d')
                                                        WHEN A.USER_BLOCKING_TIME IS NOT NULL AND A.USER_BLOCKING_TIME < A.EXTERNAL_BLOCKING_TIME
                                                        THEN DATE_FORMAT(A.USER_BLOCKING_TIME, '%Y%m%d')
                                                        ELSE DATE_FORMAT(A.EXTERNAL_BLOCKING_TIME, '%Y%m%d') END   
)
,
ODS_BASE_04 AS (  -- 按天 当天新增企微好友人数
        SELECT A.USER_ID,
                                 DATE_FORMAT(A.ADD_TIME, '%Y%m%d')  AS STATIS_DATE,
                                 COUNT(DISTINCT A.EXTERNAL_USER_ID) AS QW_NEW
        FROM QYWX_USER_CONTACT_RELATION A
        WHERE A.ADD_TIME  >=DATE'2021-01-01'
         AND  DATE_FORMAT(A.ADD_TIME, '%Y%m') >='202401' --时间参数
        GROUP BY DATE_FORMAT(A.ADD_TIME, '%Y%m%d'),A.USER_ID
)
,
ODS_BASE_05 AS (
 SELECT  DATE_FORMAT(A.JOIN_TIME, '%Y%m%d') AS STATIS_DATE,
          B.OWNER AS USER_ID,
          COUNT(DISTINCT A.UNIONID) AS GROUP_NEW
  FROM QYWX_GROUPCHAT_MEMBER A
  LEFT JOIN QYWX_GROUPCHAT_INFO B ON A.CHAT_ID = B.CHAT_ID
  LEFT JOIN DIM_CUSTOMER C ON A.UNIONID=C.UNIONID
  LEFT JOIN QYWX_EXTERNAL_USER_INFO D ON A.UNIONID=D.UNION_ID
  LEFT JOIN QYWX_USER_CONTACT_RELATION E ON D.EXTERNAL_USER_ID=E.EXTERNAL_USER_ID
        WHERE A.UNIONID IS NOT NULL
        AND   A.TYPE=2 --1 - 企业成员2 - 外部联系人
        AND   E.EXTERNAL_USER_DELETE_FLAG=0
        AND   E.USER_DELETE_FLAG =0   --企业微信好友
        AND   A.JOIN_TIME >=DATE'2021-01-01'  --进群时间
        AND   DATE_FORMAT(A.JOIN_TIME, '%Y%m')  >='202401' --时间参数
        GROUP BY DATE_FORMAT(A.JOIN_TIME, '%Y%m%d'),B.OWNER  
) 
,
ODS_BASE_06 AS (
        SELECT DATE_FORMAT(A.SUBMIT_DATE, '%Y%m%d')  AS STATIS_DATE,
       A.STAFF_ID AS USER_ID,
       COUNT(DISTINCT A.CUS_ID) AS VIPCUS_NEW
  FROM ODS_VIPSHOP_SALE A
  LEFT JOIN DIM_PRODUCT_ONLINE B ON A.PRODUCT_ID = B.PRODUCT_ID
 WHERE DATE_FORMAT(A.SUBMIT_DATE, '%Y%m%d') >='20240101' --时间参数
   AND (A.ORDER_TYPE = '14' OR A.PRODUCT_TEMPLATE_ID IN ('1935', '1936')) --购买超会
   AND A.STAFF_ID IN (SELECT DISTINCT USER_ID
                      FROM ODS_EXCEL_QYWX_ZONGBU
                      WHERE TYPE_ID = '01' ) --中鱼塘运营人员
   AND A.PAY_STATUS = '1'
   AND A.ORDER_STATUS_ID <> 7
   AND A.TOTAL_FEE > 0
   GROUP BY DATE_FORMAT(A.SUBMIT_DATE, '%Y%m%d'),A.STAFF_ID 
) 
,
ODS_BASE_07 AS (
        SELECT DATE_FORMAT(A.SUBMIT_DATE,'%Y%m%d') AS STATIS_DATE,
                                 A.STAFF_ID AS USER_ID,
                                 SUM(A.TOTAL_FEE) AS AMOUNT
        FROM ODS_VIPSHOP_SALE A
        INNER  JOIN (SELECT DISTINCT USER_ID
                                                        FROM ODS_EXCEL_QYWX_ZONGBU ) B ON A.STAFF_ID=B.USER_ID
        WHERE A.PAY_STATUS=1
        AND A.SUBMIT_DATE>=DATE'2021-01-01'
        AND DATE_FORMAT(A.SUBMIT_DATE,'%Y%m') >='202401' --时间参数
        GROUP BY DATE_FORMAT(A.SUBMIT_DATE,'%Y%m%d'),A.STAFF_ID 
)
,
ODS_BASE_08 AS (
        SELECT DATE_FORMAT(E.SUBMIT_DATE,'%Y%m%d') AS STATIS_DATE,
                                 A.USER_ID AS USER_ID,
                                 SUM(E.TOTAL_FEE) AS AMOUNT_QW , --企业微信好友销售
                                 SUM(CASE WHEN F.PRODUCT_CATEGORY_ID='A361' THEN E.TOTAL_FEE ELSE NULL END) AS AMOUNT_MM,
                                 COUNT(DISTINCT CASE WHEN (E.ORDER_TYPE='14' OR E.PRODUCT_TEMPLATE_ID IN ('1935','1936') ) AND IFNULL(E.ORDER_STATUS_ID,'') <>7  AND E.TOTAL_FEE >0
                                                                                                                 THEN  E.CUS_ID ELSE NULL END) AS VIPCUS_NEW_2
        FROM  QYWX_USER_CONTACT_RELATION A
        INNER JOIN ODS_EXCEL_QYWX_ZONGBU B ON A.USER_ID=B.USER_ID AND B.TYPE_ID='01'
        LEFT  JOIN DIM_CUSTOMER_QYWX C ON A.EXTERNAL_USER_ID=C.EXTERNAL_USER_ID
        LEFT  JOIN DIM_CUSTOMER D ON C.CUS_ID=D.CUS_ID
        LEFT  JOIN ODS_VIPSHOP_SALE E ON C.CUS_ID =E.CUS_ID
        LEFT  JOIN DIM_PRODUCT_ONLINE F ON E.PRODUCT_ID=F.PRODUCT_ID
        WHERE A.USER_DELETE_FLAG =0
                AND A.EXTERNAL_USER_DELETE_FLAG=0
                AND E.SUBMIT_DATE>=A.ADD_TIME
                AND DATE_FORMAT(E.SUBMIT_DATE,'%Y%m%d') >='20240101'
                AND E.PAY_STATUS =1
        GROUP BY A.USER_ID,DATE_FORMAT(E.SUBMIT_DATE,'%Y%m%d')  
) 
,
ODS_BASE_09 AS (
        SELECT DATE_FORMAT(F.SUBMIT_DATE,'%Y%m%d') AS STATIS_DATE,
       B.OWNER AS USER_ID,
       SUM(F.TOTAL_FEE) AS AMOUNT_GROUP,
       COUNT(DISTINCT CONCAT(CONCAT(F.CUS_ID,'&'),A.CHAT_ID)) AS GROUP_CUSNUM_BUY
  FROM QYWX_GROUPCHAT_MEMBER A
  INNER JOIN (SELECT A.CHAT_ID,
                     MAX(A.OWNER) AS OWNER
              FROM QYWX_GROUPCHAT_INFO  A
             WHERE A.NAME  LIKE '%五谷磨房丨食疗养生群%'
             AND   A.IS_DELETED ='0'
             GROUP BY A.CHAT_ID
             ) B ON A.CHAT_ID = B.CHAT_ID
  LEFT JOIN DIM_CUSTOMER C ON A.UNIONID=C.UNIONID
  INNER JOIN ODS_EXCEL_QYWX_ZONGBU D ON B.OWNER=D.USER_ID AND D.TYPE_ID='01'
  LEFT JOIN DIM_CUSTOMER_ONLINE E ON C.CUS_ID=E.CUS_ID
  LEFT JOIN QYWX_GROUPCHAT_MEMBER_CURRENT K ON A.CHAT_ID=K.CHAT_ID AND A.UNIONID=K.UNIONID
  LEFT JOIN ODS_VIPSHOP_SALE F ON C.CUS_ID =F.CUS_ID
        WHERE A.UNIONID IS NOT NULL
        AND   A.TYPE=2 --1 - 企业成员2 - 外部联系人
        AND   K.UNIONID IS NOT NULL --在群
        AND   F.PAY_STATUS =1
        AND   (F.ORDER_STATUS_ID <>7 OR F.ORDER_STATUS_ID IS NULL)
        AND   DATE_FORMAT(F.SUBMIT_DATE,'%Y%m%d') >='20240101' --时间参数
        GROUP BY B.OWNER,DATE_FORMAT(F.SUBMIT_DATE,'%Y%m%d')   
)
,
ODS_BASE_10 AS (
 SELECT   A.USER_ID,
          DATE_FORMAT(A.STAT_DATE,'%Y%m%d') AS STATIS_DATE,
          SUM(A.CHAT_CNT)AS  CHAT_CNT , --聊天总数
          IFNULL(AVG(A.REPLY_PERCENTAGE)/100,1) AS REPLY_PERCENTAGE  --回复占比
  FROM QYWX_USER_BEHAVIOR_DATA  A
  INNER JOIN ODS_EXCEL_QYWX_ZONGBU B  ON A.USER_ID=B.USER_ID AND B.TYPE_ID='01'
  WHERE DATE_FORMAT(A.STAT_DATE,'%Y%m%d') >='20240101'
   AND  DATE_FORMAT(A.STAT_DATE,'%Y%m%d') < DATE_FORMAT(NOW(),'%Y%m%d')
  GROUP BY A.USER_ID,DATE_FORMAT(A.STAT_DATE,'%Y%m%d')  
) 
,
ODS_BASE_11 AS (
        SELECT  A.YMD AS STATIS_DATE,
                                        A.USER_ID,
                                        COUNT(DISTINCT A.EXTERNAL_USER_ID) AS COMMENT_NUM,
                                        COUNT(DISTINCT CASE WHEN A.PL_FLAG='评论' THEN A.EXTERNAL_USER_ID ELSE NULL END) AS COMMENT_NUM_PL,
                                        COUNT(DISTINCT CASE WHEN A.DZ_FLAG='点赞' THEN A.EXTERNAL_USER_ID ELSE NULL END) AS COMMENT_NUM_DZ,
                                        COUNT(DISTINCT CASE WHEN B.TOTAL_FEE >0
                                                                                                                        THEN A.EXTERNAL_USER_ID ELSE NULL END) AS COMMENT_CUS_NUM,
                                        SUM(B.TOTAL_FEE) AS COMMENT_CUS_AMOUNT
        FROM
        (SELECT DATE_FORMAT(A.COMMENT_TIME,'%Y%m%d') AS YMD,
                                 A.MOMENT_USER_ID AS USER_ID,B.CUS_ID,
                                 A.EXTERNAL_USER_ID,
                                 MAX(CASE WHEN A.TYPE=1 THEN '评论' ELSE NULL END) AS PL_FLAG,
                                 MAX(CASE WHEN A.TYPE=2 THEN '点赞' ELSE NULL END) AS DZ_FLAG
                FROM QYWX_MOMENT_COMMENT_LIST A
                LEFT JOIN DIM_CUSTOMER_QYWX B ON A.EXTERNAL_USER_ID=B.EXTERNAL_USER_ID
                LEFT JOIN ODS_EXCEL_QYWX_ZONGBU C ON A.MOMENT_USER_ID=C.USER_ID
         WHERE A.TYPE IN ('1', '2') --评论和点赞
                 AND DATE_FORMAT(A.COMMENT_TIME,'%Y%m%d') >='20240101'
                 AND C.TYPE_ID='01'   --中鱼塘
                 AND A.EXTERNAL_USER_ID IS NOT NULL
        GROUP BY  DATE_FORMAT(A.COMMENT_TIME,'%Y%m%d'),A.MOMENT_USER_ID,B.CUS_ID,A.EXTERNAL_USER_ID) A
        LEFT JOIN ODS_VIPSHOP_SALE B ON A.CUS_ID=B.CUS_ID AND A.YMD =DATE_FORMAT(B.SUBMIT_DATE,'%Y%m%d') AND B.PAY_STATUS=1
        GROUP BY A.YMD,A.USER_ID 
) 
,
ODS_BASE_12 AS (
        SELECT DATE_FORMAT(A.TIMESTAMP,'%Y%m%d') AS STATIS_DATE ,
                                 B.USER_ID,
                                 COUNT(DISTINCT B.CUS_ID) AS GROUP_UV
         FROM WEBSTAT_USER_ACTION_FACT A
        INNER JOIN (SELECT B.OWNER AS USER_ID,D.CUS_ID
                                                 FROM QYWX_GROUPCHAT_MEMBER A
                                                 LEFT JOIN QYWX_GROUPCHAT_INFO B ON A.CHAT_ID = B.CHAT_ID
                                                 LEFT JOIN QYWX_GROUPCHAT_MEMBER_CURRENT C ON A.CHAT_ID=C.CHAT_ID AND A.UNIONID=C.UNIONID
                                                 LEFT JOIN DIM_CUSTOMER D ON A.UNIONID=D.UNIONID
                                                WHERE A.UNIONID IS NOT NULL
                                                AND   A.TYPE=2 --1 - 企业成员2 - 外部联系人
                                                AND   B.IS_DELETED ='0' --目前在群
                                                AND   B.NAME  LIKE '%五谷磨房丨食疗养生群%'
                                                GROUP BY B.OWNER,D.CUS_ID ) B ON A.USER_ID=B.CUS_ID
        WHERE A.TIMESTAMP >='2022-05-01'
        AND   A.TIMESTAMP >='2024-01-01'
        AND   A.USER_ID_TYPE ='CUSTOMER_ID'
        GROUP BY DATE_FORMAT(A.TIMESTAMP,'%Y%m%d'),B.USER_ID 
)
,
ODS_BASE_13 AS (
        SELECT X1.STATIS_DATE,X1.USER_ID,
                                 COUNT(DISTINCT CASE WHEN X2.FLAG='拉黑'  THEN X1.EXTERNAL_USER_ID ELSE NULL END) AS QW_NEW_QX,
                                 COUNT(DISTINCT CASE WHEN X3.TOTAL_FEE >0 THEN X1.EXTERNAL_USER_ID ELSE NULL END) AS QW_NEW_CUS
        FROM
        (SELECT DATE_FORMAT(A.ADD_TIME,'%Y%m%d') AS STATIS_DATE,
                                 A.USER_ID,A.EXTERNAL_USER_ID,D.CUS_ID
        FROM  QYWX_USER_CONTACT_RELATION A
        INNER JOIN ODS_EXCEL_QYWX_ZONGBU B ON A.USER_ID=B.USER_ID AND B.TYPE_ID='01'
        LEFT  JOIN DIM_CUSTOMER_QYWX C ON A.EXTERNAL_USER_ID=C.EXTERNAL_USER_ID
        LEFT  JOIN DIM_CUSTOMER D ON C.CUS_ID=D.CUS_ID
        WHERE A.USER_DELETE_FLAG =0
                AND A.EXTERNAL_USER_DELETE_FLAG=0
                AND DATE_FORMAT(A.ADD_TIME,'%Y%m%d')  >='20240101'
        GROUP BY A.USER_ID,DATE_FORMAT(A.ADD_TIME,'%Y%m%d'),A.EXTERNAL_USER_ID,D.CUS_ID ) X1
        LEFT JOIN (
         SELECT DATE_FORMAT(A.QXGZ_TIME,'%Y%m%d') AS STATIS_DATE,
                                        A.EXTERNAL_USER_ID,A.USER_ID,
                                        '拉黑' AS FLAG
         FROM
         (SELECT A.EXTERNAL_USER_ID,A.USER_ID,
                                 CASE WHEN MIN(A.USER_BLOCKING_TIME) IS NULL
                                                        THEN MIN(A.EXTERNAL_BLOCKING_TIME)
                                                        WHEN MIN(A.USER_BLOCKING_TIME) IS NOT NULL AND MIN(A.USER_BLOCKING_TIME) < MIN(A.USER_BLOCKING_TIME)
                                                        THEN MIN(A.USER_BLOCKING_TIME)
                                                        ELSE MIN(A.EXTERNAL_BLOCKING_TIME) END AS QXGZ_TIME
                FROM QYWX_USER_CONTACT_RELATION A
                LEFT JOIN DIM_WORKER B ON A.USER_ID =B.WORKER_ID
                INNER JOIN ODS_EXCEL_QYWX_ZONGBU C ON A.USER_ID=C.USER_ID AND C.TYPE_ID='01'
         GROUP BY A.EXTERNAL_USER_ID,A.USER_ID ) A
         WHERE DATE_FORMAT(A.QXGZ_TIME,'%Y%m%d')  >='20240101'
         GROUP BY DATE_FORMAT(A.QXGZ_TIME,'%Y%m%d'),A.EXTERNAL_USER_ID,A.USER_ID ) X2 ON X1.STATIS_DATE=X2.STATIS_DATE AND X1.USER_ID=X2.USER_ID AND X1.EXTERNAL_USER_ID=X2.EXTERNAL_USER_ID
        LEFT JOIN (SELECT   DATE_FORMAT(A.SUBMIT_DATE,'%Y%m%d') AS STATIS_DATE,A.CUS_ID,
                                                                                        SUM(A.TOTAL_FEE) AS TOTAL_FEE
                                                        FROM ODS_VIPSHOP_SALE A
                                                        WHERE A.PAY_STATUS=1
                                                        AND   DATE_FORMAT(A.SUBMIT_DATE,'%Y%m%d')  >='20240101'
                                                        GROUP BY DATE_FORMAT(A.SUBMIT_DATE,'%Y%m%d'),A.CUS_ID ) X3 ON X1.STATIS_DATE=X3.STATIS_DATE AND X1.CUS_ID=X3.CUS_ID
        GROUP BY X1.STATIS_DATE,X1.USER_ID 
) 
,
ODS_BASE_14 AS (
 SELECT X1.YMD AS STATIS_DATE,X1.USER_ID,
        COUNT(DISTINCT X1.CUS_ID) AS QW_NEW_WH,
        COUNT(DISTINCT CASE WHEN X2.FLAG='外呼' AND X3.TOTAL_FEE>0  THEN X1.CUS_ID ELSE NULL END) AS QW_NEW_CUS_WH,
        COUNT(DISTINCT CASE WHEN X2.FLAG='外呼' AND X4.FLAG='拉黑' THEN X1.CUS_ID ELSE NULL END) AS QW_NEW_QX_WH
 FROM
 (SELECT DATE_FORMAT(A.ADD_TIME,'%Y%m%d') AS YMD,
         B.CUS_ID,A.USER_ID
  FROM QYWX_USER_CONTACT_RELATION A
  LEFT JOIN DIM_CUSTOMER_QYWX B ON A.EXTERNAL_USER_ID=B.EXTERNAL_USER_ID
 WHERE DATE_FORMAT(A.ADD_TIME,'%Y%m%d')  >='20240101'
   AND (A.STATE LIKE '%百应%' OR A.STATE LIKE '%一知%')
 GROUP BY DATE_FORMAT(A.ADD_TIME,'%Y%m%d'),B.CUS_ID,A.USER_ID ) X1
 LEFT JOIN (SELECT DATE_FORMAT(A.START_TIME,'%Y%m%d') AS YMD,
                   A.CALLED_PHONE_NUMBER,B.CUS_ID,
                   '外呼' AS FLAG
            FROM ODS_AI_CALLING_RECORD A
            LEFT JOIN DIM_CUSTOMER B ON A.CALLED_PHONE_NUMBER=B.CUS_MOBILE
            WHERE DATE_FORMAT(A.START_TIME,'%Y%m%d') >='20240101'
            AND A.RESULT_STATUS IN ('已接听','ANSWERED')
            GROUP BY DATE_FORMAT(A.START_TIME,'%Y%m%d'),A.CALLED_PHONE_NUMBER,B.CUS_ID ) X2 ON X1.YMD=X2.YMD AND X1.CUS_ID=X2.CUS_ID
  LEFT JOIN (SELECT   DATE_FORMAT(A.SUBMIT_DATE,'%Y%m%d') AS YMD,A.CUS_ID,
                       SUM(A.TOTAL_FEE) AS TOTAL_FEE
            FROM ODS_VIPSHOP_SALE A
            WHERE A.PAY_STATUS=1
            AND   DATE_FORMAT(A.SUBMIT_DATE,'%Y%m%d')  >='20240101'
            GROUP BY DATE_FORMAT(A.SUBMIT_DATE,'%Y%m%d'),A.CUS_ID ) X3 ON X1.YMD=X3.YMD AND  X1.CUS_ID=X3.CUS_ID
   LEFT JOIN ( SELECT DATE_FORMAT(A.QXGZ_TIME,'%Y%m%d') AS YMD,
                      A.EXTERNAL_USER_ID,A.USER_ID,A.CUS_ID,
                      '拉黑' AS FLAG
               FROM
               (SELECT A.EXTERNAL_USER_ID,A.USER_ID,D.CUS_ID,
                     CASE WHEN MIN(A.USER_BLOCKING_TIME) IS NULL
                          THEN MIN(A.EXTERNAL_BLOCKING_TIME)
                          WHEN MIN(A.USER_BLOCKING_TIME) IS NOT NULL AND MIN(A.USER_BLOCKING_TIME) < MIN(A.USER_BLOCKING_TIME)
                          THEN MIN(A.USER_BLOCKING_TIME)
                          ELSE MIN(A.EXTERNAL_BLOCKING_TIME) END AS QXGZ_TIME
                FROM QYWX_USER_CONTACT_RELATION A
                LEFT JOIN DIM_WORKER B ON A.USER_ID =B.WORKER_ID
                INNER JOIN ODS_EXCEL_QYWX_ZONGBU C ON A.USER_ID=C.USER_ID AND C.TYPE_ID='01'
                LEFT JOIN DIM_CUSTOMER_QYWX D ON A.EXTERNAL_USER_ID=D.EXTERNAL_USER_ID
               GROUP BY A.EXTERNAL_USER_ID,A.USER_ID,D.CUS_ID ) A
               WHERE DATE_FORMAT(A.QXGZ_TIME,'%Y%m%d')  >='20240101'
        GROUP BY DATE_FORMAT(A.QXGZ_TIME,'%Y%m%d'),A.EXTERNAL_USER_ID,A.USER_ID,A.CUS_ID  ) X4 ON X1.YMD=X4.YMD AND  X1.CUS_ID=X4.CUS_ID AND X1.USER_ID=X4.USER_ID
                                 GROUP BY X1.YMD,X1.USER_ID  
 )
 ,
ODS_BASE_15 AS (
        SELECT X1.STATIS_DATE,X1.USER_ID,
                                 COUNT(DISTINCT X1.EXTERNAL_USER_ID) AS QW_NEW_BGK,
                                 COUNT(DISTINCT CASE WHEN X2.FLAG='拉黑'  THEN X1.EXTERNAL_USER_ID ELSE NULL END) AS QW_NEW_CUS_BGK,
                                 COUNT(DISTINCT CASE WHEN X3.TOTAL_FEE >0 THEN X1.EXTERNAL_USER_ID ELSE NULL END) AS QW_NEW_QX_BGK
        FROM
        (SELECT DATE_FORMAT(A.ADD_TIME,'%Y%m%d') AS STATIS_DATE,
                                 A.USER_ID,A.EXTERNAL_USER_ID,D.CUS_ID
        FROM  QYWX_USER_CONTACT_RELATION A
        INNER JOIN ODS_EXCEL_QYWX_ZONGBU B ON A.USER_ID=B.USER_ID AND B.TYPE_ID='01'
        LEFT  JOIN DIM_CUSTOMER_QYWX C ON A.EXTERNAL_USER_ID=C.EXTERNAL_USER_ID
        LEFT  JOIN DIM_CUSTOMER D ON C.CUS_ID=D.CUS_ID
        WHERE A.USER_DELETE_FLAG =0
                AND A.EXTERNAL_USER_DELETE_FLAG=0
                AND DATE_FORMAT(A.ADD_TIME,'%Y%m%d')  >='20240101'
        GROUP BY A.USER_ID,DATE_FORMAT(A.ADD_TIME,'%Y%m%d'),A.EXTERNAL_USER_ID,D.CUS_ID ) X1
        LEFT JOIN (
         SELECT DATE_FORMAT(A.QXGZ_TIME,'%Y%m%d') AS STATIS_DATE,
                                        A.EXTERNAL_USER_ID,A.USER_ID,
                                        '拉黑' AS FLAG
         FROM
         (SELECT A.EXTERNAL_USER_ID,A.USER_ID,
                                 CASE WHEN MIN(A.USER_BLOCKING_TIME) IS NULL
                                                        THEN MIN(A.EXTERNAL_BLOCKING_TIME)
                                                        WHEN MIN(A.USER_BLOCKING_TIME) IS NOT NULL AND MIN(A.USER_BLOCKING_TIME) < MIN(A.USER_BLOCKING_TIME)
                                                        THEN MIN(A.USER_BLOCKING_TIME)
                                                        ELSE MIN(A.EXTERNAL_BLOCKING_TIME) END AS QXGZ_TIME
                FROM QYWX_USER_CONTACT_RELATION A
                LEFT JOIN DIM_WORKER B ON A.USER_ID =B.WORKER_ID
                INNER JOIN ODS_EXCEL_QYWX_ZONGBU C ON A.USER_ID=C.USER_ID AND C.TYPE_ID='01'
         GROUP BY A.EXTERNAL_USER_ID,A.USER_ID ) A
         WHERE DATE_FORMAT(A.QXGZ_TIME,'%Y%m%d')  >='20240101'
         GROUP BY DATE_FORMAT(A.QXGZ_TIME,'%Y%m%d'),A.EXTERNAL_USER_ID,A.USER_ID ) X2 ON X1.STATIS_DATE=X2.STATIS_DATE AND X1.USER_ID=X2.USER_ID AND X1.EXTERNAL_USER_ID=X2.EXTERNAL_USER_ID
        LEFT JOIN (SELECT   DATE_FORMAT(A.SUBMIT_DATE,'%Y%m%d') AS STATIS_DATE,A.CUS_ID,
                                                                                        SUM(A.TOTAL_FEE) AS TOTAL_FEE
                                                        FROM ODS_VIPSHOP_SALE A
                                                        WHERE A.PAY_STATUS=1
                                                        AND   DATE_FORMAT(A.SUBMIT_DATE,'%Y%m%d')  >='20240101'
                                                        GROUP BY DATE_FORMAT(A.SUBMIT_DATE,'%Y%m%d'),A.CUS_ID ) X3 ON X1.STATIS_DATE=X3.STATIS_DATE AND X1.CUS_ID=X3.CUS_ID
        GROUP BY X1.STATIS_DATE,X1.USER_ID  
) 
,
ODS_BASE_16 AS (
 SELECT  DATE_FORMAT(D.FRIST_FANS_TIME,'%Y%m%d') AS STATIS_DATE ,
         A.USER_ID,
         COUNT(DISTINCT A.EXTERNAL_USER_ID) AS GZH_NEW_NUM
  FROM QYWX_USER_CONTACT_RELATION A
  LEFT JOIN DIM_CUSTOMER_QYWX B ON A.EXTERNAL_USER_ID = B.EXTERNAL_USER_ID
  LEFT JOIN ODS_EXCEL_QYWX_ZONGBU C ON A.USER_ID=C.USER_ID
  LEFT JOIN DIM_CUSTOMER D ON B.UNION_ID =D.UNIONID
  LEFT JOIN DIM_OPENID_B E ON B.UNION_ID=E.UNIONID
  WHERE  C.USER_NAME IS NOT  NULL  --中鱼塘
  AND    A.USER_DELETE_FLAG =0
  AND    A.EXTERNAL_USER_DELETE_FLAG=0   --目前是好友
  AND    DATE_FORMAT(D.FRIST_FANS_TIME,'%Y%m%d')  >='20240101'
  AND    D.FANS_FLAG ='Y' --关注公众号大号
  GROUP  BY  DATE_FORMAT(D.FRIST_FANS_TIME,'%Y%m%d'),A.USER_ID 
) 
,
ODS_BASE_17 AS (
 SELECT  DATE_FORMAT(E.SUBSCRIBE_TIME,'%Y%m%d') AS STATIS_DATE ,
         A.USER_ID,
         COUNT(DISTINCT A.EXTERNAL_USER_ID) AS GZH_NEW_NUM_2
  FROM QYWX_USER_CONTACT_RELATION A
  LEFT JOIN DIM_CUSTOMER_QYWX B ON A.EXTERNAL_USER_ID = B.EXTERNAL_USER_ID
  LEFT JOIN ODS_EXCEL_QYWX_ZONGBU C ON A.USER_ID=C.USER_ID
  LEFT JOIN DIM_CUSTOMER D ON B.UNION_ID =D.UNIONID
  LEFT JOIN DIM_OPENID_B E ON B.UNION_ID=E.UNIONID
  WHERE  C.USER_NAME IS NOT  NULL  --中鱼塘
  AND    A.USER_DELETE_FLAG =0
  AND    A.EXTERNAL_USER_DELETE_FLAG=0   --目前是好友
  AND    DATE_FORMAT(D.FRIST_FANS_TIME,'%Y%m%d')  >='20240101'
  AND    E.SUBSCRIBE =1 --关注公众号小号
  GROUP  BY  DATE_FORMAT(E.SUBSCRIBE_TIME,'%Y%m%d'),A.USER_ID 
)

SELECT V1.* ,V2.GROUP_CUSNUM,V3.QYH_NUM,V3.QYH_NUM_GZ,
       V3.VIPCUS_NUM,V3.REBUY_CUSNUM_7,V3.REBUY_CUSNUM_15,
       V3.REBUY_CUSNUM_30,V3.REBUY_CUSNUM_45,V3.REBUY_CUSNUM_60,V3.REBUY_CUSNUM_90,
       V3.GZH_NUM,V3.GZH_NUM_2,V3.GZH_NUM_HIS,V3.GZH_NUM_HIS_2
FROM
(SELECT 'D' AS TIME_FLAG,A.DATEKEY AS STATIS_DATE,'01' AS DATA_FLAG,
       B.USER_ID,B.USER_NAME,
       MAX(C.AVG_REPLY_TIME) AS AVG_REPLY_TIME,
       MAX(D.QW_NUM_QXGZ) AS QW_NUM_QXGZ,
       MAX(E.QW_NEW) AS QW_NEW,
       MAX(F.GROUP_NEW) AS GROUP_NEW,
       MAX(J.VIPCUS_NEW) AS VIPCUS_NEW,
       MAX(K.AMOUNT) AS AMOUNT,
       MAX(H.AMOUNT_MM) AS AMOUNT_MM,
       MAX(H.AMOUNT_QW) AS AMOUNT_QW,
       MAX(H.VIPCUS_NEW_2) AS VIPCUS_NEW_2,
       MAX(I.AMOUNT_GROUP) AS AMOUNT_GROUP,
       MAX(I.GROUP_CUSNUM_BUY) AS GROUP_CUSNUM_BUY,
       MAX(L.CHAT_CNT)  AS CHAT_CNT,
       MAX(L.REPLY_PERCENTAGE) AS REPLY_PERCENTAGE,
       MAX(M.COMMENT_NUM) AS COMMENT_NUM,
       MAX(M.COMMENT_CUS_NUM) AS COMMENT_CUS_NUM,
       MAX(M.COMMENT_CUS_AMOUNT) AS COMMENT_CUS_AMOUNT,
       MAX(M.COMMENT_NUM_DZ) AS COMMENT_NUM_DZ,
       MAX(M.COMMENT_NUM_PL) AS COMMENT_NUM_PL,
       MAX(N.GROUP_UV) AS GROUP_UV,
       MAX(O.QW_NEW_CUS) AS QW_NEW_CUS,
       MAX(O.QW_NEW_QX)  AS QW_NEW_QX,
       MAX(P.QW_NEW_CUS_WH) AS QW_NEW_CUS_WH,
       MAX(P.QW_NEW_QX_WH)  AS QW_NEW_QX_WH,
       MAX(Q.QW_NEW_BGK) AS QW_NEW_BGK,
       MAX(Q.QW_NEW_CUS_BGK) AS QW_NEW_CUS_BGK,
       MAX(Q.QW_NEW_QX_BGK) AS QW_NEW_QX_BGK,
       MAX(R.GZH_NEW_NUM) AS GZH_NEW_NUM,
       MAX(S.GZH_NEW_NUM_2) AS GZH_NEW_NUM_2
FROM DIMDATE A
LEFT JOIN ODS_BASE_01 B ON 1=1
LEFT JOIN ODS_BASE_02 C ON B.USER_ID=C.USER_ID AND A.DATEKEY=C.STATIS_DATE
LEFT JOIN ODS_BASE_03 D ON B.USER_ID=D.USER_ID AND A.DATEKEY=D.STATIS_DATE
LEFT JOIN ODS_BASE_04 E ON B.USER_ID=E.USER_ID AND A.DATEKEY=E.STATIS_DATE
LEFT JOIN ODS_BASE_05 F ON B.USER_ID=F.USER_ID AND A.DATEKEY=F.STATIS_DATE
LEFT JOIN ODS_BASE_06 J ON B.USER_ID=J.USER_ID AND A.DATEKEY=J.STATIS_DATE
LEFT JOIN ODS_BASE_07 K ON B.USER_ID=K.USER_ID AND A.DATEKEY=K.STATIS_DATE
LEFT JOIN ODS_BASE_08 H ON B.USER_ID=H.USER_ID AND A.DATEKEY=H.STATIS_DATE
LEFT JOIN ODS_BASE_09 I ON B.USER_ID=I.USER_ID AND A.DATEKEY=I.STATIS_DATE
LEFT JOIN ODS_BASE_10 L ON B.USER_ID=L.USER_ID AND A.DATEKEY=L.STATIS_DATE
LEFT JOIN ODS_BASE_11 M ON B.USER_ID=M.USER_ID AND A.DATEKEY=M.STATIS_DATE
LEFT JOIN ODS_BASE_12 N ON B.USER_ID=N.USER_ID AND A.DATEKEY=N.STATIS_DATE
LEFT JOIN ODS_BASE_13 O ON B.USER_ID=O.USER_ID AND A.DATEKEY=O.STATIS_DATE
LEFT JOIN ODS_BASE_14 P ON B.USER_ID=P.USER_ID AND A.DATEKEY=P.STATIS_DATE
LEFT JOIN ODS_BASE_15 Q ON B.USER_ID=Q.USER_ID AND A.DATEKEY=Q.STATIS_DATE
LEFT JOIN ODS_BASE_16 R ON B.USER_ID=R.USER_ID AND A.DATEKEY=R.STATIS_DATE
LEFT JOIN ODS_BASE_17 S ON B.USER_ID=S.USER_ID AND A.DATEKEY=S.STATIS_DATE
WHERE  A.DATEKEY >='20240101'
GROUP BY A.DATEKEY,B.USER_ID,B.USER_NAME ) V1
LEFT JOIN (SELECT B.OWNER AS USER_ID,
                  COUNT(DISTINCT CASE WHEN C.UNIONID IS NOT NULL THEN A.UNIONID ELSE NULL END) AS GROUP_CUSNUM --在群人数
           FROM QYWX_GROUPCHAT_MEMBER A
           LEFT JOIN QYWX_GROUPCHAT_INFO B ON A.CHAT_ID = B.CHAT_ID
           LEFT JOIN QYWX_GROUPCHAT_MEMBER_CURRENT C ON A.CHAT_ID=C.CHAT_ID AND A.UNIONID=C.UNIONID
          WHERE A.UNIONID IS NOT NULL
          AND   A.TYPE=2 --1 - 企业成员2 - 外部联系人
          AND   B.IS_DELETED ='0' --目前在群
          AND   B.NAME  LIKE '%五谷磨房丨食疗养生群%'
          GROUP BY B.OWNER ) V2 ON V1.USER_ID=V2.USER_ID
LEFT JOIN (SELECT C.USER_ID,
                  COUNT(DISTINCT A.EXTERNAL_USER_ID) AS QYH_NUM,
                  COUNT(DISTINCT CASE WHEN A.USER_DELETE_FLAG=0 AND A.EXTERNAL_USER_DELETE_FLAG=0
                                      THEN A.EXTERNAL_USER_ID ELSE NULL END) AS QYH_NUM_GZ,
                  COUNT(DISTINCT CASE WHEN A.USER_DELETE_FLAG=0 AND A.EXTERNAL_USER_DELETE_FLAG=0
                                       AND D.VIP_CUS_STATUS=1 THEN A.EXTERNAL_USER_ID ELSE NULL END) AS VIPCUS_NUM,  --20220607 企微好友当前超会身份
                  COUNT(DISTINCT CASE WHEN A.USER_DELETE_FLAG=0 AND A.EXTERNAL_USER_DELETE_FLAG=0
                                      AND  F.FIRST_BUY_TIME IS NOT NULL AND  DATE(F.SECOND_BUY_TIME) - DATE(F.FIRST_BUY_TIME) <=7
                                      THEN A.EXTERNAL_USER_ID ELSE NULL END) AS REBUY_CUSNUM_7,
                  COUNT(DISTINCT CASE WHEN A.USER_DELETE_FLAG=0 AND A.EXTERNAL_USER_DELETE_FLAG=0
                                      AND  F.FIRST_BUY_TIME IS NOT NULL AND  DATE(F.SECOND_BUY_TIME) - DATE(F.FIRST_BUY_TIME) <=15
                                      THEN A.EXTERNAL_USER_ID ELSE NULL END) AS REBUY_CUSNUM_15,
                  COUNT(DISTINCT CASE WHEN A.USER_DELETE_FLAG=0 AND A.EXTERNAL_USER_DELETE_FLAG=0
                                      AND  F.FIRST_BUY_TIME IS NOT NULL AND  DATE(F.SECOND_BUY_TIME) - DATE(F.FIRST_BUY_TIME) <=30
                                      THEN A.EXTERNAL_USER_ID ELSE NULL END) AS REBUY_CUSNUM_30,
                  COUNT(DISTINCT CASE WHEN A.USER_DELETE_FLAG=0 AND A.EXTERNAL_USER_DELETE_FLAG=0
                                      AND  F.FIRST_BUY_TIME IS NOT NULL AND  DATE(F.SECOND_BUY_TIME) - DATE(F.FIRST_BUY_TIME) <=45
                                      THEN A.EXTERNAL_USER_ID ELSE NULL END) AS REBUY_CUSNUM_45,
                  COUNT(DISTINCT CASE WHEN A.USER_DELETE_FLAG=0 AND A.EXTERNAL_USER_DELETE_FLAG=0
                                      AND  F.FIRST_BUY_TIME IS NOT NULL AND  DATE(F.SECOND_BUY_TIME) - DATE(F.FIRST_BUY_TIME) <=60
                                      THEN A.EXTERNAL_USER_ID ELSE NULL END) AS REBUY_CUSNUM_60,
                  COUNT(DISTINCT CASE WHEN A.USER_DELETE_FLAG=0 AND A.EXTERNAL_USER_DELETE_FLAG=0
                                      AND  F.FIRST_BUY_TIME IS NOT NULL AND  DATE(F.SECOND_BUY_TIME) - DATE(F.FIRST_BUY_TIME) <=90
                                      THEN A.EXTERNAL_USER_ID ELSE NULL END) AS REBUY_CUSNUM_90,
                  COUNT(DISTINCT CASE WHEN A.USER_DELETE_FLAG=0 AND A.EXTERNAL_USER_DELETE_FLAG=0 AND E.FANS_FLAG='Y'
                                      THEN A.EXTERNAL_USER_ID ELSE NULL END) AS GZH_NUM,
                  COUNT(DISTINCT CASE WHEN A.USER_DELETE_FLAG=0 AND A.EXTERNAL_USER_DELETE_FLAG=0 AND J.SUBSCRIBE=1
                                      THEN A.EXTERNAL_USER_ID ELSE NULL END) AS GZH_NUM_2,
                  COUNT(DISTINCT CASE WHEN A.USER_DELETE_FLAG=0 AND A.EXTERNAL_USER_DELETE_FLAG=0 AND (E.GZ_DATE IS NOT NULL OR E.QXGZ_DATE IS NOT NULL)
                                      THEN A.EXTERNAL_USER_ID ELSE NULL END) AS GZH_NUM_HIS,
                  COUNT(DISTINCT CASE WHEN A.USER_DELETE_FLAG=0 AND A.EXTERNAL_USER_DELETE_FLAG=0 AND J.OPENID  IS NOT NULL
                                      THEN A.EXTERNAL_USER_ID ELSE NULL END) AS GZH_NUM_HIS_2
          FROM QYWX_USER_CONTACT_RELATION A
          LEFT JOIN DIM_CUSTOMER_QYWX B ON A.EXTERNAL_USER_ID = B.EXTERNAL_USER_ID
          LEFT JOIN ODS_EXCEL_QYWX_ZONGBU C ON A.USER_ID=C.USER_ID
          LEFT JOIN DIM_CUSTOMER_ONLINE D ON B.CUS_ID =D.CUS_ID
          LEFT JOIN DIM_CUSTOMER E  ON B.CUS_ID=E.CUS_ID
          LEFT JOIN DIM_CUSTOMER_QYWX F ON B.CUS_ID =F.CUS_ID
          LEFT JOIN DIM_OPENID_B J ON E.UNIONID=J.UNIONID
          WHERE C.TYPE_ID='01'   --中鱼塘
         GROUP  BY C.USER_ID ) V3 ON V1.USER_ID=V3.USER_ID
WHERE V1.STATIS_DATE < DATE_FORMAT(NOW(),'%Y%m%d')