with T1 as (
    SELECT MEMBER_CODE, MAX(ORDER_DATETIME) ORDER_DATETIME
    FROM EC_ORDERS
    WHERE ORDER_DATETIME < '2023-07-01'
    GROUP BY MEMBER_CODE
), T2 as (
    SELECT MEMBER_CODE, MIN(ORDER_DATETIME) ORDER_DATETIME
    FROM EC_ORDERS
    WHERE ORDER_DATETIME >= '2023-07-01'
    GROUP BY MEMBER_CODE
), T as (
    SELECT T2.MEMBER_CODE, T2.ORDER_DATETIME
    FROM T2
    JOIN T1 ON T2.MEMBER_CODE = T1.MEMBER_CODE AND DATE(T2.ORDER_DATETIME, '-1 year') > T1.ORDER_DATETIME
)

SELECT
T.ORDER_DATETIME as ORDER_DATE
,T.MEMBER_CODE as CODE
,CASE WHEN M.OPTOUT_TYPE = 0 THEN '可'
      WHEN M.OPTOUT_TYPE = 1 THEN '不可'
      ELSE '不明' END as OPTOUT
FROM T
JOIN MEMBER_MST M ON T.MEMBER_CODE = M.MEMBER_CODE
ORDER BY T.ORDER_DATETIME DESC, T.MEMBER_CODE DESC