SELECT
SUBSTR(LOGIN_DATETIME, 1, 10) AS FIRST_LOGIN
,COUNT(MEMBER_CODE) AS MEMBER_CNT
FROM ACCESS_LOG
--↓このIDのMIN(LOGIN_DATETIME)を集計して表示
WHERE
MEMBER_CODE IN
(
--初回ログインが8月中のユーザidだけ取り出したい
SELECT 
DISTINCT MEMBER_CODE
FROM ACCESS_LOG
GROUP BY MEMBER_CODE
HAVING MIN(LOGIN_DATETIME) > '2023-07-31 23:59:59'
AND
MIN(LOGIN_DATETIME) < '2023-09-01 00:00:00'
)
GROUP BY FIRST_LOGIN
HAVING MIN(LOGIN_DATETIME)
ORDER BY FIRST_LOGIN DESC;