--7月以降ログイン者抽出
WITH LOGINS_AFTER_23_07
AS
(SELECT
MIN(ORDER_DATETIME) AS ORDER_DATE,
MEMBER_CODE
FROM
EC_ORDERS
WHERE DATE(ORDER_DATETIME)
BETWEEN
'2023-07-01' AND '2023-07-31'
GROUP BY
MEMBER_CODE)
SELECT DISTINCT
ORDER_DATE,
CASE WHEN
ORDER_NO NOT IN (
SELECT
ORDER_DATE
FROM
LOGINS_AFTER_23_07 t1
LEFT JOIN
EC_ORDERS t2
ON
t1.MEMBER_CODE = t2.MEMBER_CODE
WHERE
ORDER_DATETIME BETWEEN ORDER_DATE - '1day' AND ORDER_DATE - '1year'
)
AND
ORDER_NO IN(
SELECT
ORDER_DATETIME
FROM
LOGINS_AFTER_23_07 t1
LEFT JOIN
EC_ORDERS t2
ON
t1.MEMBER_CODE = t2.MEMBER_CODE
WHERE
ORDER_DATETIME < ORDER_DATE - '1year')
THEN NULL
ELSE t2.MEMBER_CODE
END AS CODE,
CASE 
WHEN
OPTOUT_TYPE=0 THEN '可'
WHEN
OPTOUT_TYPE =1 THEN '不可'
ELSE '不明'
END AS OPTOUT
FROM
LOGINS_AFTER_23_07 t1
LEFT JOIN
EC_ORDERS t2
ON
t1.MEMBER_CODE = t2.MEMBER_CODE
LEFT JOIN
MEMBER_MST t3
ON
t2.MEMBER_CODE = t3.MEMBER_CODE
ORDER BY
ORDER_DATETIME DESC,
t1.MEMBER_CODE DESC