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,
    LOGIN_DATETIME,
    strftime_d AS FIRST_LOGIN,
    COUNT(MEMBER_CODE) AS MEMBER_CNT
FROM
    Pre
GROUP BY
    LOGIN_DATETIME,MEMBER_CODE
),
Pre3 AS(
SELECT
    DISTINCT MEMBER_CODE,
    LOGIN_DATETIME,
    FIRST_LOGIN,
    MEMBER_CNT
FROM
    Pre2
GROUP BY
    MEMBER_CODE
ORDER BY
    FIRST_LOGIN DESC
)
SELECT
    DATE(FIRST_LOGIN) AS FIRST_LOGIN,
    SUM(MEMBER_CNT) AS MEMBER_CNT
FROM
    Pre3
GROUP BY
    FIRST_LOGIN
ORDER BY
    FIRST_LOGIN DESC