with a as (select *, substr(CONFIRMED_AT,1,10) as REGIST_DATE from USERS),

 b as (
select * from 
(
( SELECT "2022-08-"||substr('0000000000'||num, -2, 2) as REGIST_DATE, 
(case when num % 7 = 1 then "月" when num % 7 = 2 then "火"  when num % 7 = 3 then "水" when num % 7 = 4 then "木" when num % 7 = 5 then "金" when num % 7 = 6 then "土" else "日" end) as WK
from (WITH tmp(num) as (SELECT 1 UNION ALL SELECT num + 1 FROM tmp WHERE num < 31)
select * from tmp))

left join 
(select REGIST_DATE, count(*) as TOTAL
from a
where VALID_FLG = 1 and "2022-08-01" <= REGIST_DATE and REGIST_DATE <= "2022-08-31"
group by REGIST_DATE)
using (REGIST_DATE)

)
)

select REGIST_DATE,WK, (case when TOTAL is null then 0 else TOTAL end) as TOTAL from b