select
    min(O.ORDER_DATE) as ORDER_DATE
    , O.CODE as CODE
    , case
        when M.OPTOUT_TYPE=0 then '可'
        when M.OPTOUT_TYPE=1 then '不可'
        else '不明'
    end as OPTOUT
from 
    (
    select
        min(ORDER_DATETIME)
            over(partition by MEMBER_CODE order by ORDER_DATETIME asc rows between 1 preceding and 1 preceding) as PRE_DATE
        , ORDER_DATETIME as ORDER_DATE
        , MEMBER_CODE as CODE
    from
        EC_ORDERS
    ) as O
left join MEMBER_MST as M
    on O.CODE=M.MEMBER_CODE
where
    O.ORDER_DATE like '2023-07-%'
    and O.ORDER_DATE is not null
    and date(substr(O.PRE_DATE,1,10)) < date(substr(O.ORDER_DATE,1,10), '-1 year')
group by
    O.CODE
order by
    ORDER_DATE desc
    , CODE desc
;