with 
EC_O_LAG as (
select
    ORDER_NO
    ,MEMBER_CODE
    ,ORDER_DATETIME
    ,lag(ORDER_DATETIME) over (partition by MEMBER_CODE 
    order by ORDER_DATETIME)
    as PREV_DT
from 
    EC_ORDERS

)

,TMP1 as (
select
    ORDER_NO as ORDER_NO_
    ,MEMBER_CODE as MEMBER_CODE_
    ,min(ORDER_DATETIME) as DT2307
from 
    EC_ORDERS
where
    ORDER_DATETIME between '2023-07-01' and '2023-07-31'
group by 1,2
)

,TMP2 as (
select
    ORDER_NO
    ,MEMBER_CODE
    ,ORDER_DATETIME
    ,PREV_DT
from EC_O_LAG
    inner join TMP1
    on ORDER_NO = ORDER_NO_
        and MEMBER_CODE_ = MEMBER_CODE
where
    strftime('%s', ORDER_DATETIME) - strftime('%s', PREV_DT) >=365*24*60*60 
)

select
    T.ORDER_DATETIME
    ,T.MEMBER_CODE as CODE 
    ,case
        when OPTOUT_TYPE=0 then '可'
        when OPTOUT_TYPE=1 then '不可'
        else '不明' end as OPTOUT
from 
    TMP2 T
    left outer join 
        MEMBER_MST M
        on T.member_code = M.member_code
order by 1 desc, 2 desc