--日付に型変換
WITH A as(
SELECT
MEMBER_CODE,
strftime('%Y-%m-%d',LOGIN_DATETIME) AS LOGIN
FROM ACCESS_LOG),
--会員別にログイン日小さい順に番号を振る
B AS(
SELECT
MEMBER_CODE AS CODE,
LOGIN,
ROW_NUMBER() OVER(PARTITION BY MEMBER_CODE ORDER BY LOGIN ASC)AS NUM
FROM A
GROUP BY MEMBER_CODE),
--ログイン日別に会員ID数カウント
C AS(
SELECT LOGIN AS FIRST_LOGIN,COUNT(CODE) AS MEMBER_CNT
FROM B
WHERE NUM = 1
GROUP BY LOGIN
ORDER BY LOGIN ASC)
--初回ログインが8月のデータに絞る
SELECT *
FROM C
WHERE FIRST_LOGIN BETWEEN '2023-08-01' AND '2023-08-31';

--会員別に初めてのログイン日集計、初ログインが8月のデータに絞る
--B AS(
--SELECT MEMBER_CODE AS ID,MIN(LOGIN) AS FIRST_LOGIN
--FROM A
--WHERE FIRST_LOGIN BETWEEN '2023-08-01' AND '2023-08-31'
--GROUP BY MEMBER_CODE)
--ログイン日別に会員ID数カウント
--SELECT FIRST_LOGIN,COUNT(ID) AS MEMBER_CNT
--FROM B
--GROUP BY FIRST_LOGIN
--ORDER BY FIRST_LOGIN DESC;