-- 登録確認日時ごとに集計する
-- その日付ごとに曜日を用意する
WITH cnt(x) AS 
(
VALUES(julianday('2022-08-01'))
UNION ALL
SELECT x+1 FROM cnt WHERE x < julianday('2022-08-31')
),
-- ここ使いたい
Processed_users as (
select USER_CODE,CONFIRMED_AT
from USERS
where VALID_FLG = '1' and CONFIRMED_AT IS NOT NULL
),
count_users as(
select USER_CODE,substr(CONFIRMED_AT, 1, instr(CONFIRMED_AT, ' ') - 1) as CONFIRMED_DATE
from Processed_users
)
select CONFIRMED_DATE, COUNT(*) as cnt
from count_users
GROUP BY CONFIRMED_DATE;