select ORDER_DATETIME as ORDER_DATE, MEMBER_CODE as CODE, (case when OPTOUT_TYPE = 0 then "可" when  OPTOUT_TYPE = 1 then "不可" else "不明" end) as OPTOUT
from (
select MEMBER_CODE, ORDER_DATETIME as ORDER_DATETIME, strftime('%s',substr(ORDER_DATETIME,1,10)) - LAG(strftime('%s',substr(ORDER_DATETIME,1,10)), 1) over(partition by MEMBER_CODE order by ORDER_DATETIME) as LAG_DATE
from EC_ORDERS
)
left join MEMBER_MST using (MEMBER_CODE)
where LAG_DATE > 365*24*60*60 and ORDER_DATETIME > "2023-07-01"
order by ORDER_DATETIME desc, MEMBER_CODE desc