WITH  RECURSIVE
  cnt(x) AS (VALUES(julianday('2022-08-01'))
  UNION ALL
   SELECT x+1 FROM cnt WHERE x < julianday('2022-08-31' ))
   
SELECT strftime('%Y-%m-%d', x) AS 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(z.TOTAL2,'0') AS TOTAL
FROM cnt 
left OUTER JOIN 
(select 
date(CONFIRMED_AT) AS REGIST2_DATE,
count(*) AS TOTAL2
from USERS AS a
where a.CONFIRMED_AT >= "2022-08-01" AND a.CONFIRMED_AT <= "2022-08-31" AND a.CONFIRMED_AT not null and a.VALID_FLG='1'
group by date(CONFIRMED_AT)
) as z
ON strftime('%Y-%m-%d', x) =z. REGIST2_DATE
order by REGIST_DATE ;