SELECT
    MO.FIRST_ORDER AS ORDER_DATE
    ,M.MEMBER_CODE AS CODE
    ,CASE 
        WHEN M.OPTOUT_TYPE = '0' THEN '可' 
        WHEN M.OPTOUT_TYPE = '1' THEN '不可'
        ELSE '不明'
     END AS OPTOUT
FROM
    (
        SELECT
            MEMBER_CODE
            ,MIN(ORDER_DATETIME) AS FIRST_ORDER
        FROM
            EC_ORDERS
        WHERE
            ORDER_DATETIME BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59'
        GROUP BY
            MEMBER_CODE
    ) AS MO
    LEFT JOIN
        (
            SELECT
                MEMBER_CODE
                ,MAX(ORDER_DATETIME) AS LAST_ORDER
            FROM
                EC_ORDERS
            WHERE
                ORDER_DATETIME < '2023-07-01'
            GROUP BY
                MEMBER_CODE
        ) AS XO
    ON
        MO.MEMBER_CODE = XO.MEMBER_CODE
    LEFT JOIN
        MEMBER_MST AS M
    ON
        MO.MEMBER_CODE = M.MEMBER_CODE
WHERE
    cast(julianday(MO.FIRST_ORDER) - julianday(XO.LAST_ORDER) as integer) > 365
    
ORDER BY
    MO.FIRST_ORDER DESC
    ,MO.MEMBER_CODE DESC