WITH
---2023年7月当該顧客1件目の受注
wk_custfirst7(ORDER_NO, ORDER_DATETIME, MEMBER_CODE)
AS
 (
 SELECT
  MIN(ORDER_NO) as ORDER_NO
  ,MIN(date(ORDER_DATETIME)) as ORDER_DATETIME
  ,MEMBER_CODE
 FROM
  EC_ORDERS
 WHERE
  ORDER_DATETIME >= '2023-07-01' and ORDER_DATETIME < '2023-08-01'
 GROUP BY
  MEMBER_CODE
 )
,
---1つ前の受注
wk_custpre(ORDER_NO, ORDER_DATETIME, MEMBER_CODE)
AS
 (
 SELECT
  MAX(a.ORDER_NO) as ORDER_NO
  ,MAX(date(a.ORDER_DATETIME)) as ORDER_DATETIME
  ,a.MEMBER_CODE
 FROM
  EC_ORDERS a
  left outer join
  wk_custfirst7 b
  on
   a.MEMBER_CODE = b.MEMBER_CODE
 WHERE
   a.ORDER_DATETIME < b.ORDER_DATETIME
 GROUP BY
  a.MEMBER_CODE
 )

SELECT
 y.ORDER_DATETIME as ORDER_DATE
 ,x.MEMBER_CODE as CODE
 ,(case when m.OPTOUT_TYPE=0 then '可'  when m.OPTOUT_TYPE=1 then '不可' else '不明' end) as OPTOUT
FROM
 EC_ORDERS x
 inner join
 wk_custfirst7 y
 on
  x.MEMBER_CODE = y.MEMBER_CODE
 inner join
 wk_custpre z
 on
  x.MEMBER_CODE = z.MEMBER_CODE
 inner join
 MEMBER_MST m
 on
  x.MEMBER_CODE = m.MEMBER_CODE
WHERE
 date(y.ORDER_DATETIME, '+1 year') > z.ORDER_DATETIME
ORDER BY
 x.ORDER_DATETIME desc
 ,x.MEMBER_CODE desc