with sub as (
    select member_code, min(order_datetime) as min_datetime
    from ec_orders
    where date(order_datetime) between '2023-07-01' and '2023-07-31'
    group by member_code
)

select
    s.min_datetime as ORDER_DATE,
    -- date(s.min_datetime),
    -- max(ec.order_datetime),
    -- date(max(ec.order_datetime)),
    ec.member_code as CODE,
    case when mst.optout_type = 0 then '可' when mst.optout_type = 1 then '不可' else '不明' end as OPTOUT
from ec_orders ec
join sub s on ec.member_code = s.member_code
join member_mst mst on ec.member_code = mst.member_code
where ec.order_datetime < s.min_datetime
group by ec.member_code
having date(max(ec.order_datetime)) < date(s.min_datetime, '-1 years')
order by ORDER_DATE DESC, CODE DESC;