with ORDER_LAG_BY_MEMBER as (
    select 
        MEMBER_CODE
        , ORDER_DATETIME
        , lag(ORDER_DATETIME, 1) over(partition by MEMBER_CODE order by ORDER_DATETIME) as ORDER_LAG
    from 
        EC_ORDERS
)

select 
    min(ORDER_DATETIME) as ORDER_DATE
    , MEMBER_CODE as CODE
    --, OPTOUT_TYPE as OPTOUT
from 
    ORDER_LAG_BY_MEMBER
where 
    date(ORDER_DATETIME) between '2023-07-01' and '2023-07-31' 
    and 
    ORDER_LAG <= datetime(ORDER_DATE, '+1 years')
group by 
    MEMBER_CODE
;