SELECT
 date(z.LOGIN_DATETIME) as FIRST_LOGIN
 ,count(z.MEMBER_CODE) as MEMBER_CNT
FROM
 (
 SELECT
  x.ACCESS_ID
  , x.MEMBER_CODE
  , x.LOGIN_DATETIME
 FROM
  ACCESS_LOG x
  inner join
   (
   ---1回目を抽出
   SELECT
    MIN(ACCESS_ID) as ACCESS_ID_first
    ,MEMBER_CODE
   FROM
    ACCESS_LOG
   WHERE
    LOGIN_DATETIME >= '2023-08-01' and LOGIN_DATETIME < '2023-09-01'
   GROUP BY
    MEMBER_CODE
   ) y
  ON
   x.ACCESS_ID = y.ACCESS_ID_first
   and x.MEMBER_CODE = y.MEMBER_CODE
 ) z
GROUP BY
 FIRST_LOGIN
ORDER BY
 FIRST_LOGIN desc