with base as (
select
    ACCESS_ID
    ,MEMBER_CODE
    ,date(LOGIN_DATETIME) as LOGIN_DATETIME
    ,rank() over (partition by MEMBER_CODE order by LOGIN_DATETIME) as RANK
from
    ACCESS_LOG
where
    date(LOGIN_DATETIME) between date("2023-08-01") and date("2023-08-31")
)
select
    LOGIN_DATETIME as FIRST_LOGIN
    ,count() as MEMBER_CNT
from
    base
where
    RANK =1
group by
    FIRST_LOGIN
order by
    FIRST_LOGIN desc