SELECT T1.ORDER_DATETIME AS ORDER_DATE,
       T1.MEMBER_CODE AS CODE,
       (CASE WHEN T3.OPTOUT_TYPE = '0' THEN '可'
             WHEN T3.OPTOUT_TYPE = '1' THEN '不可'
             ELSE '不明'
        END
       ) AS OPTOUT
  FROM (SELECT MEMBER_CODE ,
               ORDER_DATETIME,
               DATE(ORDER_DATETIME) AS DEF_DATE
          FROM EC_ORDERS
         WHERE DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
        ) T1 INNER JOIN (SELECT MEMBER_CODE ,
                                MAX(DATE(ORDER_DATETIME)) AS LAST_DATE
                           FROM EC_ORDERS
                          WHERE DATE(ORDER_DATETIME) < '2023-07-01'
                          GROUP BY MEMBER_CODE
                        ) T2
                ON T1.MEMBER_CODE = T2.MEMBER_CODE
             INNER JOIN MEMBER_MST T3
                ON T1.MEMBER_CODE = T3.MEMBER_CODE
 WHERE CAST(julianday(T1.DEF_DATE) - julianday(T2.LAST_DATE) as integer) > 365
 ORDER BY ORDER_DATE DESC,CODE DESC