with nana as
(select 
 MEMBER_CODE as member
 ,min(date(ORDER_DATETIME)) as maxdate
from EC_ORDERS
where date(ORDER_DATETIME) between date('2023-07-01') and date('2023-07-31')
group by member
)
,joken as
(select
 member
 ,maxdate
 ,date(maxdate,'localtime', '-1 year') as jokenbi
from nana
)
,izen as
(select
 MEMBER_CODE as member
 ,max(date(ORDER_DATETIME)) as choimae
from EC_ORDERS
where  date(ORDER_DATETIME) < date('2023-07-01')
group by member
)
,okaeri as
(select
 joken.member
 ,joken.maxdate
 ,joken.jokenbi 
 ,izen.choimae 
from joken
left join izen
on joken.member = izen.member
where date(izen.choimae) <joken.jokenbi
)
select
 okaeri.maxdate as ORDER_DATE
 ,okaeri.member as MEMBER
 ,case
   when MEMBER_MST.OPTOUT_TYPE =0 then '可'
   when MEMBER_MST.OPTOUT_TYPE =1 then '不可'
   else '不明'
  end as OPTOUT
from okaeri
inner join MEMBER_MST
on okaeri.member= MEMBER_MST.MEMBER_CODE
order by ORDER_DATE desc ,MEMBER desc;