--EC受注データ(EC_ORDERS)より、2023年7月の１カ月間に受注データがあり、
WITH LIST1 AS
(SELECT MEMBER_CODE,ORDER_DATETIME,
FIRST_VALUE(ORDER_DATETIME)
OVER(
PARTITION BY MEMBER_CODE
ORDER BY ORDER_DATETIME)  AS FV
FROM EC_ORDERS
WHERE DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31')
,LIST2 AS 
(SELECT
MEMBER_CODE,FV AS ORDER_DATE
FROM LIST1
GROUP BY MEMBER_CODE,FV)
--1つ前の受注データの受注日の差が1年以上ある会員
,LIST3 AS
(SELECT MEMBER_CODE,ORDER_DATETIME,
LAST_VALUE(ORDER_DATETIME)
OVER(
PARTITION BY MEMBER_CODE
ORDER BY ORDER_DATETIME
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LV
FROM EC_ORDERS
WHERE DATE(ORDER_DATETIME) < '2023-07-01')
,LIST4 AS 
(SELECT
MEMBER_CODE,LV
FROM LIST3
GROUP BY MEMBER_CODE,LV)
--対象者絞り込み
,LIST5 AS
(SELECT
MEMBER_CODE,ORDER_DATE
FROM LIST2
JOIN LIST4
USING(MEMBER_CODE)
WHERE julianday(DATE(ORDER_DATE)) - julianday(DATE(LV)) > 365)
--まとめ
SELECT
ORDER_DATE,
MEMBER_CODE AS CODE,
--オプトアウト区分(OPTOUT_TYPE)が、
--0 の場合は「可」、1 の場合は「不可」、それ以外は「不明」とOPTOUT欄に表示しなさい。
CASE OPTOUT_TYPE
WHEN 0 THEN '可'
WHEN 1 THEN '不可'
ELSE '不明' END AS OPTOUT
FROM LIST5
LEFT JOIN MEMBER_MST
USING(MEMBER_CODE)
ORDER BY 1 DESC,2 DESC
;