with tmp as(
select
    m.member_code CODE
    ,e.order_datetime ORDER_DATE
    ,case
        when m.optout_type = 0 then '可'
        when m.optout_type = 1 then '不可'
        else '不明'
    end as OPTOUT
from
    ec_orders e left join member_mst m 
    on e.member_code = m.member_code
where
    date(e.order_datetime) between '2023-07-01' and '2023-07-31'
), tmp2 as (
select
    m.member_code CODE
    ,e.order_datetime ORDER_DATE
from
    ec_orders e left join member_mst m 
    on e.member_code = m.member_code
where
    date(e.order_datetime) < '2023-07-01'
)
select
    min(t.order_date) ORDER_DATE
    ,t.code
    ,t.optout
from
    tmp t inner join tmp2 t2
    on t.code = t2.code
where
    datetime(t2.order_date) < datetime(datetime(t.order_date, '-1 years') , '-1 days')
group by
    t.code, t.optout
order by
    t.order_date desc, t.code desc
;