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.REGIST_DATE,
 case strftime('%w', date(A.REGIST_DATE))
    WHEN '0' THEN '日'
    WHEN '1' THEN '月'
    WHEN '2' THEN '火'
    WHEN '3' THEN '水'
    WHEN '4' THEN '木'
    WHEN '5' THEN '金'
    WHEN '6' THEN '土'
    ELSE NULL
    END AS WK,
 coalesce(B.TOTAL,0) AS TOTAL
FROM
 (
   SELECT date(x) AS REGIST_DATE FROM cnt 
 ) as A
 left join
 (  
  select
 date(CONFIRMED_AT) AS REGIST_DATE,
 COUNT(*) 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)
) as B
on A.REGIST_DATE  = B.REGIST_DATE