WITH MEMBERS_2023_JULY_ORDER AS (
    SELECT A.MEMBER_CODE AS CODE, MIN(ORDER_DATETIME) AS ORDER_DATETIME
    FROM MEMBER_MST AS A INNER JOIN EC_ORDERS AS B
        ON A.MEMBER_CODE = B.MEMBER_CODE
    WHERE DATE(ORDER_DATETIME) BETWEEN "2023-07-01" AND "2023-07-31"
    GROUP BY A.MEMBER_CODE
),
MEMBERS_LAST_ORDER AS (
    SELECT A.MEMBER_CODE AS CODE, MAX(ORDER_DATETIME) AS ORDER_DATETIME
    FROM MEMBER_MST AS A INNER JOIN EC_ORDERS AS B
        ON A.MEMBER_CODE = B.MEMBER_CODE
    WHERE DATE(ORDER_DATETIME) < "2023-07-01"
    GROUP BY A.MEMBER_CODE
)

SELECT A.CODE, A.ORDER_DATETIME,
    CASE
        WHEN OPTOUT_TYPE = "0" THEN "可"
        WHEN OPTOUT_TYPE = "1" THEN "不可"
        ELSE "不明"
    END AS OPTOUT
FROM MEMBER_MST AS M INNER JOIN MEMBERS_2023_JULY_ORDER AS A
        ON M.MEMBER_CODE = A.CODE
    INNER JOIN MEMBERS_LAST_ORDER AS B
        ON A.CODE = B.CODE
WHERE DATE(B.ORDER_DATETIME) < DATE(A.ORDER_DATETIME, "-1 YEAR")
ORDER BY A.ORDER_DATETIME DESC, A.CODE DESC