with 
    sub as (
    select 
        MEMBER_CODE
        ,MAX(ORDER_DATETIME) ORDER_DATETIME
    from EC_ORDERS
    where ORDER_DATETIME < '2023-07-01'
    group by MEMBER_CODE
    )
    ,sub2 as (
    select 
        MEMBER_CODE
        ,MIN(ORDER_DATETIME) ORDER_DATETIME
    from EC_ORDERS
    where ORDER_DATETIME >= '2023-07-01'
    group by MEMBER_CODE
    )
    ,sub3 as (
    select
        sub2.MEMBER_CODE
        ,sub2.ORDER_DATETIME
    from 
        sub2
        join sub 
            on sub2.MEMBER_CODE = sub.MEMBER_CODE 
            AND DATE(sub2.ORDER_DATETIME, '-1 year') > sub.ORDER_DATETIME
)
select
    sub3.ORDER_DATETIME as ORDER_DATE
    ,sub3.MEMBER_CODE as CODE
    ,CASE WHEN MEMBER_MST.OPTOUT_TYPE = 0 THEN '可'
          WHEN MEMBER_MST.OPTOUT_TYPE = 1 THEN '不可'
          ELSE '不明' END as OPTOUT
from 
    sub3
    join MEMBER_MST
        on sub3.MEMBER_CODE = MEMBER_MST.MEMBER_CODE
ORDER BY 
    ORDER_DATE desc
    ,CODE desc