select O.M_ORDER_DATETIME as ORDER_DATE
    , O.MEMBER_CODE as CODE
    , case M.OPTOUT_TYPE when 0 then '可' when 1 then '不可' else '不明' end as OPTOUT

from 
( select MEMBER_CODE
    ,min(ORDER_DATETIME) as M_ORDER_DATETIME
    ,min(date(ORDER_DATETIME)) as M_ORDER_DATE
 from EC_ORDERS
 where ORDER_DATETIME between '2023-07-01' and '2023-07-31'
 group by MEMBER_CODE
) as O
 INNER JOIN MEMBER_MST as M ON M.MEMBER_CODE = O.MEMBER_CODE
 
where
    -- 一年以上前に購入履歴がある
    exists (select MEMBER_CODE from EC_ORDERS 
        where DATE(ORDER_DATETIME) < DATE(M_ORDER_DATE ,  '-1 year')
        and MEMBER_CODE = O.MEMBER_CODE
    )
    -- 一年間の購入履歴がない
    and not exists ( select MEMBER_CODE from EC_ORDERS
    where DATE(ORDER_DATETIME) between DATE(M_ORDER_DATE , '-1 year') and DATE(M_ORDER_DATE , '-1 day')
            and MEMBER_CODE = O.MEMBER_CODE
    )
 
order by ORDER_DATE desc , CODE desc