WITH JulyFirstOrders AS (
  SELECT MEMBER_CODE, MIN(ORDER_DATETIME) as FirstOrderInJuly
  FROM EC_ORDERS
  WHERE ORDER_DATETIME BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59'
  GROUP BY MEMBER_CODE
),
PreviousOrders AS (
  SELECT MEMBER_CODE, MAX(DATETIME(ORDER_DATETIME)) as LastOrderBeforeJuly
  FROM EC_ORDERS
  WHERE DATETIME(ORDER_DATETIME) < DATETIME('2023-07-01 00:00:00')
  GROUP BY MEMBER_CODE
)
-- select * from PreviousOrders where member_code in ( '10010', '10030')
-- union
-- select * from JulyFirstOrders where member_code in ( '10010', '10030')
SELECT 
  DATETIME(j.FirstOrderInJuly) 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 JulyFirstOrders j
JOIN PreviousOrders p ON j.MEMBER_CODE = p.MEMBER_CODE
JOIN MEMBER_MST m ON m.MEMBER_CODE = j.MEMBER_CODE
WHERE julianday(j.FirstOrderInJuly) - julianday(p.LastOrderBeforeJuly) >= 366
order by 1 desc, 2 desc

-- select * from EC_ORDERS
--  where member_code in ( '10030')