WITH SUB1 AS (
    SELECT
        MEMBER_CODE
        , ORDER_DATETIME
        , DATE(ORDER_DATETIME) AS ORDER_DATE
        , DATE(LAG(ORDER_DATETIME) OVER (PARTITION BY MEMBER_CODE)) AS ORDER_DATE_BFR
    FROM
        EC_ORDERS
),
SUB2 AS (
    SELECT
        MEMBER_CODE
        , ROW_NUMBER() OVER (PARTITION BY MEMBER_CODE) AS RNUM
        , ORDER_DATETIME
        , CASE WHEN ORDER_DATE_BFR IS NULL THEN -1 ELSE JULIANDAY(ORDER_DATE) - JULIANDAY(ORDER_DATE_BFR) END AS KEIKA
    FROM 
        SUB1
    WHERE
        ORDER_DATE BETWEEN '2023-07-01' AND '2023-07-31'
)
SELECT
    ORDER_DATETIME AS ORDER_DATE
    , a.MEMBER_CODE AS CODE
    , CASE b.OPTOUT_TYPE WHEN 0 THEN '可' WHEN 1 THEN '不可' ELSE '不明' END
FROM
    SUB2 a
    INNER JOIN MEMBER_MST b ON
        a.MEMBER_CODE = b.MEMBER_CODE
WHERE
    RNUM = 1
    AND KEIKA > 365
ORDER BY
    ORDER_DATETIME DESC
    , a.MEMBER_CODE DESC
;