WITH MAX_NUM AS (SELECT *
                   FROM EC_ORDERS A
                   LEFT JOIN(SELECT ORDER_NO,
                                    MEMBER_CODE,
                                    ORDER_DATETIME,
                                    ROW_NUMBER() OVER(PARTITION BY MEMBER_CODE ORDER BY ORDER_DATETIME ASC) AS NUM
                               FROM EC_ORDERS
                              ORDER BY MEMBER_CODE,
                                       DATE(ORDER_DATETIME)
                             ) B
                    ON A.ORDER_NO = B.ORDER_NO
                 WHERE '2023-07-01' <= DATE(A.ORDER_DATETIME)   
                   AND DATE(A.ORDER_DATETIME) <= '2023-07-31'  
                   AND B.NUM <> 1
                 GROUP BY A.MEMBER_CODE
                )
SELECT A.ORDER_DATETIME2 AS ORDER_DATE,
       A.MEMBER_CODE AS CODE,
       CASE WHEN B.OPTOUT_TYPE = 0 THEN '可'
            WHEN B.OPTOUT_TYPE = 1 THEN '不可'
            ELSE '不明'
       END AS OPTOUT
  FROM(SELECT A.ORDER_NO,
              A.MEMBER_CODE,
              A.ORDER_DATETIME,
              A.NUM,
              MAX_NUM.ORDER_NO AS ORDER_NO2,
              MAX_NUM.ORDER_DATETIME AS ORDER_DATETIME2,
              MAX_NUM.NUM AS NUM2
         FROM (SELECT ORDER_NO,
                      MEMBER_CODE,
                      ORDER_DATETIME,
                      ROW_NUMBER() OVER(PARTITION BY MEMBER_CODE ORDER BY ORDER_DATETIME ASC) AS NUM
                 FROM EC_ORDERS
                ORDER BY MEMBER_CODE,  
                         DATE(ORDER_DATETIME)
              ) A
         LEFT JOIN MAX_NUM
           ON A.MEMBER_CODE = MAX_NUM.MEMBER_CODE
          AND A.NUM = MAX_NUM.NUM -1
         WHERE MAX_NUM.ORDER_NO IS NOT NULL
           AND DATE(A.ORDER_DATETIME) < DATE(MAX_NUM.ORDER_DATETIME, 'localtime', '-1 year')
       ) A
  LEFT JOIN MEMBER_MST B
    ON A.MEMBER_CODE = B.MEMBER_CODE
 ORDER BY A.ORDER_DATETIME2 DESC,
          A.MEMBER_CODE DESC