with tmp as
(
SELECT
ORDER_DATETIME as ORDER_DATE,
m.MEMBER_CODE as CODE,
CASE

WHEN m.OPTOUT_TYPE = 0 THEN '可'
WHEN m.OPTOUT_TYPE = 1 THEN '不可'
ELSE '不明' 
END as OPTOUT

from EC_ORDERS o left join MEMBER_MST m on o.MEMBER_CODE=m.MEMBER_CODE


), tmp2 as (
select 
julianday(ORDER_DATE) - julianday(date(lag(ORDER_DATE,1) OVER(
 PARTITION BY CODE
 ORDER BY ORDER_DATE
))) jdate

, ORDER_DATE,lag(ORDER_DATE,1) OVER(
 PARTITION BY CODE
 ORDER BY ORDER_DATE
),CODE, OPTOUT

from tmp
),tmp3 as(
select member_code , date(min(e.order_datetime)) as order_date
from EC_ORDERS e where
e.order_datetime >= '2023-07-01 00:00:00'
group by member_code
)


select order_date ,
CODE,OPTOUT from tmp2
where ORDER_DATE >= '2023-07-01 00:00:00'
and exists (select 1 from tmp3  where 
tmp2.code=tmp3.member_code
and date(tmp2.order_date)=tmp3.order_date
)
and jdate>=366
order by ORDER_DATE desc, CODE desc