WITH SUB1 AS(
    --7月に購買したMEMBER_CODE
    SELECT
        MEMBER_CODE,
        MIN(DATE(ORDER_DATETIME)) AS ORDER_DATE202307
    FROM
        EC_ORDERS
    WHERE
        DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
    GROUP BY
        MEMBER_CODE
    ),
    SUB2 AS(
    --会員の7月より前の最終購買日
    SELECT
        MEMBER_CODE,
        MAX(DATE(ORDER_DATETIME)) AS ORDER_DATE_BEFORE202307
    FROM
        EC_ORDERS
    WHERE
        DATE(ORDER_DATETIME) < '2023-07-01'
    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 '不可'
        ELSE '不明'
    END AS OPTOUT
FROM (
    SUB1
    INNER JOIN SUB2
        ON SUB1.MEMBER_CODE=SUB2.MEMBER_CODE
        ) AS SUB
    INNER JOIN MEMBER_MST AS MM
        ON MM.MEMBER_CODE=SUB.MEMBER_CODE
WHERE
    ORDER_DATE_BEFORE202307 < DATE(ORDER_DATE202307,'-1 years')
ORDER BY
    ORDER_DATE DESC,
    CODE DESC
    ;