WITH LAST AS (
    SELECT
        MIN(EC_ORDERS.ORDER_DATETIME) AS LAST_ORDER_DATE
        , MEMBER_CODE AS CODE
    FROM
        EC_ORDERS
    WHERE
        ORDER_DATETIME BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59'
), ONE_PREVIOUS AS (
    SELECT
         MAX(EC_ORDERS.ORDER_DATETIME) AS ONE_PREVIOUS_ORDER_DATE
        , MEMBER_CODE AS CODE
    FROM
        EC_ORDERS
    WHERE
        ONE_PREVIOUS_ORDER_DATE < '2023-07-01 00:00:00'
)
SELECT 
    LAST_ORDER_DATE AS ORDER_DATE
    , E.MEMBER_CODE AS CODE
    , CASE
        OPTOUT_TYPE
        WHEN 0 THEN "可"
        WHEN 1 THEN "不可"
        ELSE "不明"
        END AS OPTOUT
FROM 
    MEMBER_MST M, EC_ORDERS E
JOIN
    LAST
ON
    M.MEMBER_CODE = LAST.MEMBER_CODE
JOIN
    ONE_PREVIOUS
ON
    MEMBER_MST.MEMBER_CODE = ONE_PREVIOUS.MEMVER_CODE
WHERE 
    LAST_ORDER_DATE - ONE_PREVIOUS_ORDER_DATE > 1
ORDER BY 
    ORDER_DATETIME DESC
    , MEMBER_CODE DESC;