with st as (select 
MEMBER_CODE,
MIN(ORDER_DATETIME) as MIN_OD
from ec_orders
where date(ORDER_DATETIME) between '2023-07-01' and '2023-07-31'
group by MEMBER_CODE
)
, st2 as (select 
MEMBER_CODE,
MAX(ORDER_DATETIME) as MAX_OD
from ec_orders
where ORDER_DATETIME < '2023-07-01'
group by MEMBER_CODE
)
-- select unixepoch(date('2023-07-15')) - unixepoch(date('2022-07-15'))
select st.MIN_OD as ORDER_DATE, st.MEMBER_CODE as CODE, 
case 
when MEMBER_MST.OPTOUT_TYPE == 0 then '可'
when MEMBER_MST.OPTOUT_TYPE == 1 then '不可'
else '不明'
end as OPTOUT
from st join st2 on st.MEMBER_CODE = st2.MEMBER_CODE
join MEMBER_MST on st.MEMBER_CODE = MEMBER_MST.MEMBER_CODE
where st2.MAX_OD < DATE(st.MIN_OD, '-1 years')
-- where (unixepoch(date(st.MIN_OD)) - unixepoch(date(st2.MAX_OD))) > 31536000
order by ORDER_DATE desc, CODE desc