SELECT T1.MIN_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 ,
               MIN(ORDER_DATETIME) AS MIN_DATETIME
          FROM EC_ORDERS
         WHERE DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
         GROUP BY MEMBER_CODE
       ) T1 INNER JOIN (SELECT MEMBER_CODE ,
                            MAX(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.MIN_DATETIME) - julianday(T2.LAST_DATE) as integer) > 365
ORDER BY ORDER_DATE DESC,CODE DESC