WITH JulyOrders AS (
    -- 2023年7月の各会員の最初の受注日とその前の受注日を特定
    SELECT 
        o1.MEMBER_CODE,
        MIN(o1.ORDER_DATETIME) AS FIRST_JULY_ORDER,
        MAX(o2.ORDER_DATETIME) AS PREVIOUS_ORDER
    FROM EC_ORDERS o1
    LEFT JOIN EC_ORDERS o2 ON o1.MEMBER_CODE = o2.MEMBER_CODE AND o2.ORDER_DATETIME < o1.ORDER_DATETIME
    GROUP BY o1.MEMBER_CODE
    HAVING PREVIOUS_ORDER LIKE '2023-07%'
)

SELECT 
    j.FIRST_JULY_ORDER AS ORDER_DATE,
    j.MEMBER_CODE AS CODE,
    CASE 
        WHEN m.OPTOUT_TYPE = 0 THEN '可'
        WHEN m.OPTOUT_TYPE = 1 THEN '不可'
        ELSE '不明'
    END AS OPTOUT
FROM JulyOrders j
JOIN MEMBER_MST m ON j.MEMBER_CODE = m.MEMBER_CODE
ORDER BY j.FIRST_JULY_ORDER DESC, j.MEMBER_CODE DESC
;