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', '/', '-'))
)
, USERS1 AS (SELECT
	DATE(CONFIRMED_AT) REGIST_DATE1
	,COUNT(*) TOTAL
FROM
     USERS
WHERE
	VALID_FLG = '1'
GROUP BY
	DATE(CONFIRMED_AT)
)
	
SELECT strftime('%Y-%m-%d', x) REGIST_DATE
    ,CASE (strftime('%w', strftime('%Y-%m-%d', x)))
		WHEN '0' THEN '日'
		WHEN '1' THEN '月'
		WHEN '2' THEN '火'
		WHEN '3' THEN '水'
		WHEN '4' THEN '木'
		WHEN '5' THEN '金'
		WHEN '6' THEN '土'
	END AS WK
	,IFNULL(USERS1.total, 0) AS TOTAL
FROM cnt LEFT OUTER JOIN USERS1 ON USERS1.REGIST_DATE1 = REGIST_DATE
;