with st as (
select
member_code
, min(order_datetime) as fd
, date(min(order_datetime)) as fdate
from ec_orders
where date(order_datetime) between '2023-07-01' and '2023-07-31'
group by member_code
)
, pre as (
select 
member_code
, max(order_datetime) as mtime
from ec_orders where order_datetime < '2023-07'
group by member_code
)
select 
st.fd as ORDER_DATE
, st.member_code as CODE
, case member_mst.OPTOUT_TYPE
  when 0 then '可'
  when 1 then '不可'
  else  '不明'
  end as OPTOUT
from st join member_mst on st.member_code = member_mst.member_code
where exists
(
select 1 from pre where st.member_code = pre.member_code and pre.mtime < date(st.fdate, '-1 year')
)
order by ORDER_DATE desc, CODE desc