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 DATETIME ,
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')
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;