WITH
  JulyFirstOrders AS (
    SELECT
      MIN(ORDER_DATETIME) AS FirstOrderInJuly
    , MEMBER_CODE
    FROM
      EC_ORDERS
    WHERE
      DATE (ORDER_DATETIME) >= '2023-07-01'
      AND DATE (ORDER_DATETIME) <= '2023-07-31'
    GROUP BY
      MEMBER_CODE
  )
, PreviousOrders AS (
    SELECT
      MAX(ORDER_DATETIME) AS LastOrderBeforeJuly
    , MEMBER_CODE
    FROM
      EC_ORDERS
    WHERE
      DATE (ORDER_DATETIME) < '2023-07-01'
    GROUP BY
      MEMBER_CODE
  )
SELECT
  J.FirstOrderInJuly AS ORDER_DATE
, J.MEMBER_CODE AS CODE
, CASE
    WHEN M.OPTOUT_TYPE = 0 THEN '可'
    WHEN M.OPTOUT_TYPE = 1 THEN '不可'
    ELSE '不明'
  END AS OPTOUT
FROM
  JulyFirstOrders J
  INNER JOIN PreviousOrders P ON J.MEMBER_CODE = P.MEMBER_CODE
  INNER JOIN MEMBER_MST M ON J.MEMBER_CODE = M.MEMBER_CODE
WHERE
  P.LastOrderBeforeJuly <= DATE (J.FirstOrderInJuly, '-1 year')
ORDER BY
  J.FirstOrderInJuly DESC
, J.MEMBER_CODE DESC;