with sub as (
    select 
        MEMBER_CODE,
        min(ORDER_DATETIME) as ORDER_DATETIME,
        min(date(ORDER_DATETIME)) as m_date
    from EC_ORDERS
    where 
        date(ORDER_DATETIME) between '2023-07-01' and '2023-07-31'
    group by
        MEMBER_CODE
)
select 
    SUB.ORDER_DATETIME as ORDER_DATE,
    sub.MEMBER_CODE as CODE,
    case mm.OPTOUT_TYPE 
        when 0 then '可'
        when 1 then '不可'
        else '不明'
    end as OPTOUT
from sub
inner join MEMBER_MST as mm on sub.MEMBER_CODE = mm.MEMBER_CODE
where 
    exists( 
        select
            MEMBER_CODE 
        from
            EC_ORDERS 
        where
            sub.MEMBER_CODE = MEMBER_CODE 
            and date(ORDER_DATETIME) < date(m_date,'-1 year')
    ) 
    and not exists( 
        select
            MEMBER_CODE 
        from
            EC_ORDERS 
        where
            sub.MEMBER_CODE = MEMBER_CODE 
            and date(ORDER_DATETIME) between date(m_date,'-1 year') and date(m_date,'-1 day')
    )
order by
    ORDER_DATETIME desc,
    CODE desc;