WITH ABC AS(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY MEMBER_CODE) AS "LOG_CNT"
        ,MEMBER_CODE
        ,Date(LOGIN_DATETIME) AS "FIRST_LOGIN"
    FROM
        ACCESS_LOG
    ORDER BY
        MEMBER_CODE, "FIRST_LOGIN"
)
SELECT
    "FIRST_LOGIN"
    ,COUNT(MEMBER_CODE) AS "MEMBER_CNT"
FROM
    ABC
WHERE
    "LOG_CNT" = 1 and "FIRST_LOGIN" between "2023-08-01" and "2023-08-31"
GROUP BY
    "FIRST_LOGIN"
ORDER BY
    "FIRST_LOGIN" DESC
;