SELECT
SUBSTR(LOGIN_DATETIME, 1, 10) AS FIRST_LOGIN
,COUNT(MEMBER_CODE) AS MEMBER_CNT
FROM ACCESS_LOG



WHERE
MEMBER_CODE 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;