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;