SELECT
    FIRST_LOGIN
    , COUNT(DISTINCT MEMBER_CODE) AS MEMBER_CNT
FROM (
    SELECT
        DATE(MIN(LOGIN_DATETIME) OVER (PARTITION BY MEMBER_CODE)) AS FIRST_LOGIN
        , MEMBER_CODE
    FROM ACCESS_LOG
)
WHERE FIRST_LOGIN BETWEEN '2023-08-01' and '2023-08-31'
GROUP BY DATE(FIRST_LOGIN)
ORDER BY FIRST_LOGIN DESC
;