SELECT
    ORDER_DATETIME AS ORDER_DATE,
    o.MEMBER_CODE AS CODE,
    (CASE OPTOUT_TYPE
        WHEN 0 THEN '可'
        WHEN 1 THEN '不可'
        ELSE '不明'
    END) AS OPTOUT
FROM
    (SELECT
        *,
        LAG(ORDER_DATETIME) OVER (ORDER BY MEMBER_CODE, ORDER_DATETIME) AS before,
        julianday(ORDER_DATETIME) - julianday(LAG(ORDER_DATETIME) OVER (ORDER BY MEMBER_CODE, ORDER_DATETIME)) AS datediff
    FROM
        EC_ORDERS
    ) o
LEFT JOIN
    MEMBER_MST AS m
    ON o.MEMBER_CODE = m.MEMBER_CODE
WHERE
    ORDER_DATE BETWEEN '2023-07-01' AND '2023-08-01'
    AND datediff >= 365
ORDER BY
    ORDER_DATETIME DESC,
    o.MEMBER_CODE DESC
;