with ju as(SELECT MIN(ORDER_DATETIME) ORDER_DATE,MEMBER_CODE CODE FROM EC_ORDERS where ORDER_DATETIME like '2023-07%' group by MEMBER_CODE),
juo as(SELECT max(ORDER_DATETIME) MOD,MEMBER_CODE CODE FROM EC_ORDERS where substr(ORDER_DATETIME,0,8) < '2023-07'
group by MEMBER_CODE
)
select JU.*,
  CASE MM.OPTOUT_TYPE
    WHEN 0 THEN '可'
    WHEN 1 THEN '不可'
    ELSE '不明'
  END OPTOUT
  /*
  ,DATE(JU.ORDER_DATE, '-12 months'),JUO.MOD te
  ,STRFTIME('%Y%m%d',JUO.MOD)
  ,STRFTIME('%Y%m%d',DATE(JU.ORDER_DATE, '-12 months'))
  */
FROM Ju
left JOIN MEMBER_MST MM  ON JU.CODE = MM.MEMBER_CODE
left JOIN JUO ON JU.CODE=JUO.CODE
where STRFTIME('%Y%m%d',JUO.MOD) < STRFTIME('%Y%m%d',DATE(JU.ORDER_DATE, '-12 months'))
order by JU.ORDER_DATE desc,2 desc