WITH TMP AS(
  SELECT
    MEMBER_CODE,
    ORDER_DATETIME AS LATEST,
    LEAD(ORDER_DATETIME, 1) OVER(PARTITION BY MEMBER_CODE ORDER BY ORDER_DATETIME DESC) AS PREV
  FROM
    EC_ORDERS
  ORDER BY
    1,
    2 DESC
),
TMP2 AS(
  SELECT
    MEMBER_CODE,
    MAX(LATEST) AS LATEST,
    MAX(PREV) AS PREV
  FROM
    TMP
  WHERE
    DATETIME(LATEST, '-1 YEAR') >= PREV
  GROUP BY
    1
)
SELECT
  LATEST AS ORDER_DATE,
  MEMBER_CODE AS CODE,
  CASE
    WHEN OPTOUT_TYPE = 0 THEN '可'
    WHEN OPTOUT_TYPE = 1 THEN '不可'
    ELSE '不明'
  END AS OPTOUT
FROM
  TMP2
  INNER JOIN
    MEMBER_MST
  USING
    (MEMBER_CODE)
ORDER BY
  1 DESC,
  2 DESC