with  July_Order as (
select
a.MEMBER_CODE
,min(a.ORDER_DATETIME) as ORDER_DATETIME
from
EC_ORDERS a
inner join
(select
MEMBER_CODE
,ORDER_NO
,min(ORDER_DATETIME) as min_date
from
EC_ORDERS 
where
strftime('%Y%m', ORDER_DATETIME) = '202307'
group by
MEMBER_CODE
,ORDER_NO
) b
on  
a.MEMBER_CODE = b.MEMBER_CODE
and CAST(SUBSTR(b.ORDER_NO, 2) AS INTEGER) - 1000 = a.ORDER_NO
and strftime('%Y%m%d', b.min_date, '-1 year') > strftime('%Y%m%d', a.ORDER_DATETIME)
and strftime('%Y%m%d', a.ORDER_DATETIME) <  strftime('%Y%m%d', b.min_date, '-1 year') or strftime('%Y%m%d', a.ORDER_DATETIME) > strftime('%Y%m%d', b.min_date, '-1 year')
where
strftime('%Y%m', a.ORDER_DATETIME) = '202307'
group by
a.MEMBER_CODE
)
select
b.ORDER_DATETIME as ORDER_DATE
,a.MEMBER_CODE as CODE
,case OPTOUT_TYPE when '0' then '可' when '1' then '不可' else '不明' end as OPTOUT
from
MEMBER_MST a 
inner join July_Order b 
on a.MEMBER_CODE = b.MEMBER_CODE
order by
ORDER_DATETIME desc
,CODE desc
;