WITH SUB1 AS(
    --7月に購買したMEMBER_CODE
    SELECT
        MEMBER_CODE,
        MIN(ORDER_DATETIME) AS ORDER_DATE202307
    FROM
        EC_ORDERS
    WHERE
        ORDER_DATETIME BETWEEN '2023-07-01' AND '2023-07-31'
    GROUP BY
        MEMBER_CODE
    ),
    SUB2 AS(
    --7月に購買がある会員の1つ前の購買日
    SELECT
        MEMBER_CODE,
        MAX(ORDER_DATETIME) AS ORDER_DATE_BEFORE202307
    FROM
        EC_ORDERS
    WHERE
        ORDER_DATETIME < '2023-07-01'
        AND MEMBER_CODE IN (SELECT MEMBER_CODE FROM SUB1)
    GROUP BY
        MEMBER_CODE
    )
SELECT
    ORDER_DATE202307 AS 'ORDER_DATE',
    SUB1.MEMBER_CODE AS 'CODE',
    CASE 
        WHEN OPTOUT_TYPE=0 THEN '可'
        WHEN OPTOUT_TYPE=1 THEN '不可'
        WHEN OPTOUT_TYPE=2 THEN '不明'
    END AS OPTOUT
FROM
    SUB1 INNER JOIN SUB2 ON SUB1.MEMBER_CODE=SUB2.MEMBER_CODE
    INNER JOIN MEMBER_MST AS MM
        ON SUB1.MEMBER_CODE=MM.MEMBER_CODE
WHERE
    ORDER_DATE_BEFORE202307 < DATE(ORDER_DATE202307,'-1 years')
ORDER BY
    ORDER_DATE DESC,
    CODE DESC
;