with latter as(
select MEMBER_CODE as CODE,
min(ORDER_DATETIME) as ORDER_DATE
from EC_ORDERS
where DATE(ORDER_DATETIME) >= "2023-07-01"
group by CODE
),
former as(
select MEMBER_CODE as CODE,
max(DATE(ORDER_DATETIME)) as ORDER_DATE
from EC_ORDERS
where DATE(ORDER_DATETIME) < "2023-07-01"
group by CODE
),
ord as(
select l.CODE as CODE,
l.ORDER_DATE as latter,
f.ORDER_DATE as former,
(strftime('%s', DATE(l.ORDER_DATE)) - strftime('%s', f.ORDER_DATE)) /(3600*24) as diff
from latter l left join former f on f.CODE = l.CODE
)
select 
ord.latter as ORDER_DATE,
ord.CODE as CODE,
case
when OPTOUT_TYPE = 0 then "可"
when OPTOUT_TYPE = 1 then "不可"
else "不明"
end as OPTOUT
--diff, ord.former
from ord
left join MEMBER_MST on ord.CODE = MEMBER_MST.MEMBER_CODE
where diff > 365
order by 1 desc,2 desc