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', '/', '-') ))

SELECT
	A.DAY REGIST_DATE
	,CASE (strftime('%w', DATE(A.DAY)))
		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(COUNT(*), 0) TOTAL
FROM
	(SELECT strftime('%Y-%m-%d', x) AS DAY FROM cnt) AS A
	LEFT JOIN USERS ON DATE(CONFIRMED_AT) = A.DAY
WHERE
	A.DAY BETWEEN '2022-08-01' AND '2022-08-31'
	AND VALID_FLG = '1'
GROUP BY
	A.DAY
;