with ZD AS(
 -- 1つ前の受注データ
    SELECT  
    MAX(DATETIME(ORDER_DATETIME)) AS Z_DATE
    ,MEMBER_CODE
FROM EC_ORDERS
WHERE
    DATE(ORDER_DATETIME) < '2023-07-01'  
GROUP BY MEMBER_CODE
)
,KFD AS(
 -- 2023年7月に入り最初に受注したデータ
    SELECT  
    MIN(DATETIME(ORDER_DATETIME)) AS K_F_DATE
    ,MEMBER_CODE
FROM EC_ORDERS
WHERE
    DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31' 

GROUP BY MEMBER_CODE
)

SELECT
        K_F_DATE AS ORDER_DATE
--        ,Z_DATE
--        ,DATETIME(K_F_DATE, '-1 years')
    ,   KFD.MEMBER_CODE AS CODE
    ,   CASE    WHEN OPTOUT_TYPE = 0 THEN '可'
                WHEN OPTOUT_TYPE = 1 THEN '不可'
            ELSE '不明'
        END AS OPTOUT
FROM KFD 
    INNER JOIN ZD
    ON KFD.MEMBER_CODE = ZD.MEMBER_CODE
    INNER JOIN MEMBER_MST
    ON KFD.MEMBER_CODE = MEMBER_MST.MEMBER_CODE

WHERE
  -- 差が1年以上
    DATE(K_F_DATE,'-1 years') > DATE(Z_DATE)

ORDER BY
       ORDER_DATE DESC
      ,CODE DESC
;