WITH
  sub AS (
    SELECT
      MEMBER_CODE,
      MIN(ORDER_DATETIME) AS ORDER_DATETIME,
      MIN(DATE (ORDER_DATETIME)) AS M_DATE
    FROM
      EC_ORDERS
    WHERE
      DATE (ORDER_DATETIME) BETWEEN "2023-07-01" AND "2023-07-31"
    GROUP BY
      MEMBER_CODE
  )
SELECT
  sub.ORDER_DATETIME AS ORDER_DATE,
  sub.MEMBER_CODE AS CODE,
  CASE mm.OPTOUT_TYPE
    WHEN 0 THEN "可"
    WHEN 1 THEN "不可"
    ELSE "不明"
  END AS OPTOUT
FROM
  sub
  INNER JOIN MEMBER_MST AS mm ON mm.MEMBER_CODE = sub.MEMBER_CODE
WHERE
  EXISTS (
    SELECT
      ORDER_NO
    FROM
      EC_ORDERS
    WHERE
      sub.MEMBER_CODE = MEMBER_CODE
      AND DATE (ORDER_DATETIME) < DATE (sub.M_DATE, "-1 year")
  )
  AND NOT EXISTS (
    SELECT
      ORDER_NO
    FROM
      EC_ORDERS
    WHERE
      sub.MEMBER_CODE = MEMBER_CODE
      AND DATE (ORDER_DATETIME) BETWEEN DATE (sub.M_DATE, "-1 year") AND DATE  (sub.M_DATE, "-1 day")
  )
ORDER BY
  sub.ORDER_DATETIME desc,
  sub.MEMBER_CODE desc;