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 '土'
        else 'undefined'
    end as WK,
  ifnull(z.TOTAL,'0') AS TOTAL
FROM cnt 
left JOIN 
(select 
date(CONFIRMED_AT) AS REGIST2_DATE,
count(*) AS TOTAL
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;