with
    jul as (
        select
            member_code
            ,min(date(order_datetime)) as order_date
            ,order_datetime
        from
            ec_orders
        where
            order_datetime >= '2023-07-01 00:00:00'
            and order_datetime < '2023-08-01 00:00:00'
        group by
            1
    )
    ,former as (
        select
            member_code
            ,max(date(order_datetime)) as last_order_date
        from
            ec_orders
        where
            order_datetime < '2023-07-01 00:00:00'
        group by
            1
    )

 
select
    jul.order_datetime as ORDER_DATE
    ,jul.member_code as CODE
    ,case
        when optout_type = 0 then '可'
        when optout_type = 1 then '不可'
        else '不明'
        end as OPTOUT
from
    jul
join
    former
on
    jul.order_date >= date(former.last_order_date, '+366 days')
    and jul.member_code = former.member_code
join
    member_mst as mm
on
    jul.member_code = mm.member_code
order by
    1 desc, 2 desc