WITH
    ORD_WK AS(
        SELECT
            ORD.ORDER_DATETIME
          , ORD.MEMBER_CODE
          , LEAD(ORD.ORDER_DATETIME, 1) OVER (PARTITION BY ORD.MEMBER_CODE ORDER BY ORD.ORDER_DATETIME DESC) AS PREV_DATE
          FROM
            EC_ORDERS AS ORD
    )
SELECT
    ORD_WK.ORDER_DATETIME AS ORDER_DATE
  , ORD_WK.MEMBER_CODE    AS CODE
  , CASE M_MST.OPTOUT_TYPE
        WHEN 0 THEN '可'
        WHEN 1 THEN '不可'
        ELSE '不明'
    END AS OPTOUT
  FROM
    ORD_WK INNER JOIN MEMBER_MST AS M_MST
        ON ORD_WK.MEMBER_CODE = M_MST.MEMBER_CODE
 WHERE
       STRFTIME('%Y%m', ORD_WK.ORDER_DATETIME) = '202307'
   AND ORD_WK.PREV_DATE IS NOT NULL
   AND ORD_WK.PREV_DATE < DATE(ORD_WK.ORDER_DATETIME, '-1 YEARS')
 ORDER BY
       ORD_WK.ORDER_DATETIME DESC
     , ORD_WK.MEMBER_CODE    DESC
;