WITH RECURSIVE cnt(x) AS (
    VALUES(julianday(replace('2022/08/01', '/', '-') ))
    UNION ALL
    SELECT x+1 FROM cnt WHERE x < julianday(replace('2022/08/31', '/', '-') )
)
,DATE_LIST_TEMP AS (
    SELECT strftime('%Y-%m-%d', x) AS DATE FROM cnt
)
,DATE_LIST AS (
    SELECT DATE, substr('日月火水木金土', strftime("%w", DATE, 'localtime')+1, 1) AS WK FROM DATE_LIST_TEMP
)
,USER_CNT AS(
    SELECT SUBSTR(CONFIRMED_AT,1,10) AS DATE, COUNT(USER_CODE) AS CNT
    FROM USERS
    WHERE VALID_FLG='1' AND CONFIRMED_AT IS NOT NULL AND SUBSTR(CONFIRMED_AT,1,7) = '2022-08'
    GROUP BY SUBSTR(CONFIRMED_AT,1,10)
)
SELECT DATE_LIST.DATE AS REGIST_DATE, DATE_LIST.WK, IFNULL(USER_CNT.CNT,0) AS TOTAL
FROM DATE_LIST
LEFT JOIN USER_CNT
ON DATE_LIST.DATE = USER_CNT.DATE