SELECT
    LOGS.ORDER_DATETIME AS ORDER_DATE,
    -- PREV_LOGS.PREV_ORDER_DATETIME,
    LOGS.MEMBER_CODE AS CODE,
    CASE MEMBER_MST.OPTOUT_TYPE
        WHEN 0 THEN "可"
        WHEN 1 THEN "不可"
        ELSE "不明"
    END AS OPTOUT
FROM (
    -- MEMBER_CODE毎の2023年7月中の最初の受注
    SELECT
        MEMBER_CODE,
        MIN(ORDER_DATETIME) AS ORDER_DATETIME
    FROM
        EC_ORDERS
    WHERE
        ORDER_DATETIME >= "2023-07-01 00:00:00"
        AND ORDER_DATETIME < "2023-08-01 00:00:00"
    GROUP BY
        MEMBER_CODE
) AS LOGS
LEFT JOIN
    MEMBER_MST
ON
    LOGS.MEMBER_CODE = MEMBER_MST.MEMBER_CODE
LEFT JOIN (
    -- 1つ前の注文
    SELECT
        *
    FROM (
        SELECT
            ORDER_DATETIME,
            MEMBER_CODE,
            LAG(ORDER_DATETIME) OVER(PARTITION BY MEMBER_CODE ORDER BY ORDER_DATETIME ASC) AS PREV_ORDER_DATETIME
        FROM
            EC_ORDERS
    )
    WHERE
        PREV_ORDER_DATETIME IS NOT NULL
) AS PREV_LOGS
ON
    LOGS.MEMBER_CODE = PREV_LOGS.MEMBER_CODE
    AND LOGS.ORDER_DATETIME = PREV_LOGS.ORDER_DATETIME
WHERE
    PREV_LOGS.PREV_ORDER_DATETIME IS NOT NULL
    AND DATE(LOGS.ORDER_DATETIME, "-1 YEAR") > DATE(PREV_LOGS.PREV_ORDER_DATETIME)
 
ORDER BY
    LOGS.ORDER_DATETIME DESC,
    LOGS.MEMBER_CODE DESC