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','/','-'))),
 
--人数カウントしたテーブルを2つ作り、最後にUNIONする
cnt1 AS(
 SELECT
  DATE(CONFIRMED_AT) AS REGIST_DATE,
  COUNT(USER_CODE) AS TOTAL
 FROM
  USERS
 WHERE
  VALID_FLG = '1'
 AND
  DATE(CONFIRMED_AT) BETWEEN DATE('2022-08-01') AND DATE('2022-08-31')
 GROUP BY
  DATE(CONFIRMED_AT)
 ),
 
cnt2 AS
 (
 SELECT
  DATE(strftime('%Y-%m-%d',x)) AS REGIST_DATE,
  0 AS TOTAL
 FROM
  cnt
 ),
 
cnt3 AS(SELECT
 *
FROM
 cnt1
UNION
SELECT
 *
FROM
 cnt2
)

SELECT
 REGIST_DATE,
 CASE
  WHEN strftime('%w',REGIST_DATE) = '0' THEN  '日'
  WHEN strftime('%w',REGIST_DATE) = '1' THEN  '月'
  WHEN strftime('%w',REGIST_DATE) = '2' THEN  '火'
  WHEN strftime('%w',REGIST_DATE) = '3' THEN  '水'
  WHEN strftime('%w',REGIST_DATE) = '4' THEN  '木'
  WHEN strftime('%w',REGIST_DATE) = '5' THEN  '金'
  WHEN strftime('%w',REGIST_DATE) = '6' THEN  '土'
 END AS WK,
 SUM(TOTAL) AS TOTAL
FROM
 cnt3
GROUP BY
 REGIST_DATE,WK
ORDER BY
 REGIST_DATE