with o7 as (
-- 7月の最小の受注日と最初の１年前の日付取得
select o7.MEMBER_CODE
, min(o7.ORDER_DATETIME) ORDER_DATETIME  -- ７月最小受注日
, date(datetime(min(o7.ORDER_DATETIME),'-12 months')) ORDER_DATETIME12M -- ７月最小受注日の1年前
from EC_ORDERS o7
where o7.ORDER_DATETIME >= '2023-07-01'
and o7.ORDER_DATETIME < '2023-08-01'
group by o7.MEMBER_CODE
)
, t as (
select o7.MEMBER_CODE
, o7.ORDER_DATETIME
, max(ec.ORDER_DATETIME) B4ORDER_DATETIME
from o7
     inner join EC_ORDERS ec
       on ec.MEMBER_CODE = o7.MEMBER_CODE
       and ec.ORDER_DATETIME < o7.ORDER_DATETIME -- ７月最小受注日以前の受注抽出
group by o7.MEMBER_CODE
having max(ec.ORDER_DATETIME) < ORDER_DATETIME12M -- 取得した受注日が1年前以前
)
select
 t.ORDER_DATETIME ORDER_DATE
, t.MEMBER_CODE CODE
, case when m.OPTOUT_TYPE = 0 then '可'
       when m.OPTOUT_TYPE = 1 then '不可'
       else '不明'
  end OPTOUT
from t
     inner join MEMBER_MST m
       on m.MEMBER_CODE = t.MEMBER_CODE
order by
 t.ORDER_DATETIME desc
, m.MEMBER_CODE desc
;