WITH TMP AS(
  SELECT
    MEMBER_CODE,
    MIN(ORDER_DATETIME) AS JULY_FIRST
  FROM
    EC_ORDERS
  WHERE
    ORDER_DATETIME >= '2023-07-01'
  GROUP BY
    1
),
TMP2 AS(
  SELECT
    MEMBER_CODE,
    JULY_FIRST,
    MAX(ORDER_DATETIME) AS PREV
  FROM
    TMP
    INNER JOIN
      EC_ORDERS
    USING(MEMBER_CODE)
  WHERE
    ORDER_DATETIME < '2023-07-01'
  GROUP BY
    1
  HAVING DATE(JULY_FIRST, '-1 YEAR') > PREV
)
SELECT
  JULY_FIRST AS ORDER_DATE,
  MEMBER_CODE AS CODE,
  CASE
    WHEN OPTOUT_TYPE = 0 THEN '可'
    WHEN OPTOUT_TYPE = 1 THEN '不可'
    ELSE '不明'
  END AS OPTOUT
FROM
  TMP2
  INNER JOIN
    MEMBER_MST USING(MEMBER_CODE)
ORDER BY
  1 DESC,
  2 DESC