WITH Pre AS(
    SELECT
        ACCESS_ID,
        MEMBER_CODE,
        DATE(LOGIN_DATETIME) AS FIRST_LOGIN,
        RANK() OVER(PARTITION BY MEMBER_CODE ORDER BY DATE(LOGIN_DATETIME)) AS rank 
    FROM
        ACCESS_LOG
    GROUP BY
        DATE(LOGIN_DATETIME),
        MEMBER_CODE
    ORDER BY
        MEMBER_CODE
)
SELECT
    FIRST_LOGIN,
    COUNT(MEMBER_CODE) AS MEMBER_CNT
FROM
    Pre
WHERE
    rank = 1
AND
    FIRST_LOGIN BETWEEN '2023-08-01' AND '2023-08-31'
GROUP BY
    FIRST_LOGIN
ORDER BY
    FIRST_LOGIN DESC