WITH Pre AS(
SELECT
    ACCESS_ID,
    MEMBER_CODE,
    LOGIN_DATETIME,
    strftime('%Y-%m', LOGIN_DATETIME) AS strftime,
    strftime('%Y-%m-%d', LOGIN_DATETIME) AS strftime_d,
    LOGIN_DATETIME
FROM
    ACCESS_LOG
WHERE
    strftime = '2023-08'
ORDER BY
    strftime_d DESC
),
Pre2 AS(
SELECT
    MEMBER_CODE,
    strftime_d AS first_login,
    COUNT(MEMBER_CODE) AS CNT
FROM
    Pre
GROUP BY
    MEMBER_CODE,first_login
ORDER BY
    first_login DESC
),
Pre3 AS(
SELECT
    MEMBER_CODE,
    first_login,
    CNT
FROM
    Pre2
GROUP BY
    MEMBER_CODE
ORDER BY
    first_login desc
)
SELECT
    first_login,
    SUM(CNT) AS cnt
FROM
    Pre3
GROUP BY
    first_login
ORDER BY
    first_login DESC