with T as (
    --顧客ごとに7月最初の購入日と1つ前の最大購入日
    select
    e.member_code
    ,m
    ,max(e.order_datetime) as ma
    from ec_orders as e
    inner join (
        select
        member_code
        ,min(order_datetime) as m
        from ec_orders
        where order_datetime between '2023-07-01 00:00:00' and '2023-07-31 23:59:59'
        group by member_code
    ) as sub
    on sub.member_code = e.member_code
    where e.order_datetime < sub.m
    group by e.member_code
)

select
m as ORDER_DATE
,T.member_code as CODE
,case mm.optout_type
    when 0 then '可'
    when 1 then '不可'
    else '不明'
end as OPTOUT
from T
inner join member_mst as mm
on T.member_code= mm.member_code
where date(ma) < date(m,'-1 year')
order by ORDER_DATE desc,CODE desc