SELECT
    DATE(ORDER_DATETIME) AS ORDER_DATE,
    MEMBER_CODE AS CODE,
    OPTOUT
FROM (
    SELECT
        *,
        LAG(ORDER_DATETIME) OVER(PARTITION BY MEMBER_CODE ORDER BY ORDER_DATETIME ASC) AS PREV_ORDER_DATETIME
    FROM (
        SELECT
            EC_ORDERS.ORDER_DATETIME,
            EC_ORDERS.MEMBER_CODE,
            CASE MEMBER_MST.OPTOUT_TYPE
                WHEN 0 THEN "可"
                WHEN 1 THEN "不可"
                ELSE "不明"
            END AS OPTOUT
        FROM
            EC_ORDERS
        LEFT JOIN
            MEMBER_MST
        ON
            EC_ORDERS.MEMBER_CODE = MEMBER_MST.MEMBER_CODE
        INNER JOIN (
            SELECT
                DISTINCT
                    MEMBER_CODE
            FROM
                EC_ORDERS
            WHERE
                ORDER_DATETIME >= "2023-07-01 00:00:00"
                AND ORDER_DATETIME < "2023-08-01 00:00:00"
        ) AS LOG
        ON
            EC_ORDERS.MEMBER_CODE = LOG.MEMBER_CODE
    )
)
WHERE
    ORDER_DATETIME >= "2023-07-01 00:00:00"
    AND ORDER_DATETIME < "2023-08-01 00:00:00"
    AND PREV_ORDER_DATETIME IS NOT NULL
    AND STRFTIME("%s", ORDER_DATETIME) - STRFTIME("%s", PREV_ORDER_DATETIME) >= 365 * 24 * 60
ORDER BY
    ORDER_DATETIME DESC,
    MEMBER_CODE DESC