--会員別に初ログイン日を集計
WITH A AS(
SELECT MEMBER_CODE,MIN(DATE(LOGIN_DATETIME)) AS FIRST_LOGIN
FROM ACCESS_LOG
GROUP BY MEMBER_CODE),

--初ログイン日降順別に会員数集計
B AS (SELECT FIRST_LOGIN,COUNT(MEMBER_CODE) AS MEMBER_CNT
FROM A
GROUP BY FIRST_LOGIN
ORDER BY FIRST_LOGIN DESC)

--初ログイン日が8月のデータに絞る
SELECT FIRST_LOGIN,MEMBER_CNT
FROM B
WHERE FIRST_LOGIN BETWEEN '2023-08-01' AND '2023-08-31';