with first_order_in_july as (
  select
      MEMBER_CODE 
    , min(ORDER_DATETIME) as FIRST_ORDER_DATE_IN_JULY
  from 
      EC_ORDERS
  where
      '2023-07-01' <= ORDER_DATETIME and ORDER_DATETIME < '2023-08-01'
  group by
      MEMBER_CODE

), last_order_before_july as (
  select
      MEMBER_CODE 
    , max(ORDER_DATETIME) as LAST_ORDER_DATE_BEFORE_JULY
  from 
      EC_ORDERS
  where
      ORDER_DATETIME < '2023-07-01' 
  group by
      MEMBER_CODE

), OKAERI_MEMBER as (
  select
      FIRST_ORDER_DATE_IN_JULY as ORDER_DATETIME
    , MEMBER_CODE
  from
                 first_order_in_july    a
      inner join last_order_before_july b using(MEMBER_CODE)
  where
      date(b.LAST_ORDER_DATE_BEFORE_JULY) < date(FIRST_ORDER_DATE_IN_JULY, '-1 year')
)
select
    o.ORDER_DATETIME    as ORDER_DATE
  , o.MEMBER_CODE       as CODE
  , case
        when m.OPTOUT_TYPE = 0 then '可'
        when m.OPTOUT_TYPE = 1 then '不可'
        else                        '不明'
    end as OPTOUT
from
           OKAERI_MEMBER o 
inner join MEMBER_MST    m using(MEMBER_CODE)
order by
    o.ORDER_DATETIME desc
  , o.MEMBER_CODE    desc
;