with tbl7 as (
select
    min(ORDER_DATETIME) as ORDER_DATETIME
    ,min(date(ORDER_DATETIME)) as OD_DATE
    ,MEMBER_CODE
from
    EC_ORDERS
where
    date(ORDER_DATETIME) between date("2023-07-01") and date("2023-07-31")
group by
    MEMBER_CODE
)
,tbl0 as (
select
    max(date(ORDER_DATETIME)) as OD_DATE
    ,MEMBER_CODE
from
    EC_ORDERS
where
    date(ORDER_DATETIME) < date("2023-07-01") 
group by
    MEMBER_CODE
)
,target as (
select
    tbl7.ORDER_DATETIME
    ,tbl7.MEMBER_CODE
from
    tbl7
    left join
    tbl0
    on tbl7.MEMBER_CODE = tbl0.MEMBER_CODE
where
    date(tbl0.OD_DATE,"+1 year")<tbl7.OD_DATE
)
select
    ORDER_DATETIME as ORDER_DATE
    ,t.MEMBER_CODE as CODE
    ,case 
        when m.OPTOUT_TYPE ="0" then "可"
        when m.OPTOUT_TYPE ="1" then "不可"
        else "不明"
    end as OPTOUT
from
    target as t
    inner join
    MEMBER_MST as m
    on t.MEMBER_CODE= m.MEMBER_CODE
order by
    ORDER_DATETIME desc
    ,t.MEMBER_CODE desc