SELECT
    ORDER_DATE
    , MEMBER_CODE AS CODE
    , (
        CASE OPTOUT_TYPE
            WHEN 0 THEN '可'
            WHEN 1 THEN '不可'
            ELSE '不明'
        END
    ) AS OPTOUT
FROM
    (
        SELECT
            MEMBER_CODE
            , MIN(ORDER_DATETIME) AS ORDER_DATE
            , (
                SELECT
                    MAX(EO2.ORDER_DATETIME)
                FROM
                    EC_ORDERS AS EO2
                WHERE
                    EO1.MEMBER_CODE = EO2.MEMBER_CODE
                    AND EO2.ORDER_DATETIME < '2023-07-01 00:00:00'
            ) AS PREV_ORDER_DATE
        FROM
            EC_ORDERS AS EO1
        WHERE
            ORDER_DATETIME BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59'
        GROUP BY
            MEMBER_CODE
    )
INNER JOIN
    MEMBER_MST USING(MEMBER_CODE)
WHERE
    JULIANDAY(ORDER_DATE) - JULIANDAY(PREV_ORDER_DATE) >= 366
ORDER BY
    ORDER_DATE DESC
    , MEMBER_CODE DESC
;