WITH FIRST_ORDER_IN_202307 AS
(
  SELECT
    MEMBER_CODE,
    MIN(ORDER_DATETIME) AS FIRST_ORDER_DATETIME
  FROM
    EC_ORDERS
  WHERE
    ORDER_DATETIME BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59'
  GROUP BY
    MEMBER_CODE
),
LAST_ORDER_BEFORE_202307 AS
(
  SELECT
    MEMBER_CODE,
    MAX(ORDER_DATETIME) AS LAST_ORDER_DATETIME
  FROM
    EC_ORDERS
  WHERE
    ORDER_DATETIME < '2023-07-01 00:00:00'
  GROUP BY
    MEMBER_CODE
)
SELECT
  FO.FIRST_ORDER_DATETIME AS ORDER_DATE,
  FO.MEMBER_CODE AS CODE,
  CASE MM.OPTOUT_TYPE
    WHEN 0 THEN '可'
    WHEN 1 THEN '不可'
    ELSE '不明'
  END OPTOUT
FROM
  (
    FIRST_ORDER_IN_202307 AS FO
    LEFT OUTER JOIN
      LAST_ORDER_BEFORE_202307 AS LO
      ON
        FO.MEMBER_CODE = LO.MEMBER_CODE
  )
  INNER JOIN
    MEMBER_MST AS MM
    ON
      FO.MEMBER_CODE = MM.MEMBER_CODE
WHERE
  DATE(LO.LAST_ORDER_DATETIME) < DATE(FO.FIRST_ORDER_DATETIME, '-1 year')
ORDER BY
  FO.FIRST_ORDER_DATETIME DESC,
  FO.MEMBER_CODE DESC