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
        MEMBER_CODE,
        ORDER_DATETIME,
        LAG(ORDER_DATETIME) OVER (PARTITION BY MEMBER_CODE ORDER BY ORDER_DATETIME),
        julianday(ORDER_DATETIME) - julianday(LAG(ORDER_DATETIME) OVER (PARTITION BY MEMBER_CODE ORDER BY 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 >= 366
ORDER BY
    ORDER_DATETIME DESC,
    o.MEMBER_CODE DESC
;