SELECT
SUBSTR(LOGIN_DATETIME, 1, 10) AS FIRST_LOGIN
,COUNT(MEMBER_CODE) AS MEMBER_CNT
FROM ACCESS_LOG
WHERE
MEMBER_CODE NOT IN
(
--初回ログインが8月以降のユーザidだけ取り出したい
SELECT 
MEMBER_CODE
FROM ACCESS_LOG
GROUP BY MEMBER_CODE
HAVING MIN(LOGIN_DATETIME) < '2023-08-01 00:00:00'
)
GROUP BY FIRST_LOGIN
ORDER BY FIRST_LOGIN DESC;