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