--【問題2】
WITH BASE AS (
SELECT
	SUBSTR(LOGIN_DATETIME,1,10) AS FIRST_LOGIN
,	MEMBER_CODE
,	RANK() OVER(PARTITION BY MEMBER_CODE ORDER BY SUBSTR(LOGIN_DATETIME,1,10)) AS RANKING
FROM
	ACCESS_LOG
)
SELECT
	FIRST_LOGIN
,	COUNT(DISTINCT MEMBER_CODE) AS MEMBER_CNT
FROM
	BASE
WHERE
	RANKING = 1
GROUP BY
	FIRST_LOGIN
HAVING
	FIRST_LOGIN >= '2023-08-01'
AND	FIRST_LOGIN < '2023-09-01'
ORDER BY
	FIRST_LOGIN DESC