with tmp as(
select MEMBER_CODE,ORDER_DATETIME,
lag(ORDER_DATETIME)
over(partition by MEMBER_CODE
     order by ORDER_DATETIME) as LAG_ORDER_DATETIME
  from EC_ORDERS),
tmp2 as(
select MEMBER_CODE,ORDER_DATETIME
  from tmp
 where date(LAG_ORDER_DATETIME,'+12 month') < ORDER_DATETIME
   and substr(ORDER_DATETIME,1,10) between '2023-07-01' and '2023-07-31')
select b.ORDER_DATETIME as ORDER_DATE,a.MEMBER_CODE as CODE,
case OPTOUT_TYPE when 0 then '可'
                 when 1 then '不可'
                 else '不明' end as OPTOUT
  from MEMBER_MST a
  join tmp2 b
    on a.MEMBER_CODE = b.MEMBER_CODE
order by ORDER_DATETIME desc,CODE desc