with vDat as
(select date('2022-08-01') d union all
 select date('2022-08-02') d union all
 select date('2022-08-03') d union all
 select date('2022-08-04') d union all
 select date('2022-08-05') d union all
 select date('2022-08-06') d union all
 select date('2022-08-07') d union all
 select date('2022-08-08') d union all
 select date('2022-08-09') d union all
 select date('2022-08-10') d union all
 select date('2022-08-11') d union all
 select date('2022-08-12') d union all
 select date('2022-08-13') d union all
 select date('2022-08-14') d union all
 select date('2022-08-15') d union all
 select date('2022-08-16') d union all
 select date('2022-08-17') d union all
 select date('2022-08-18') d union all
 select date('2022-08-19') d union all
 select date('2022-08-20') d union all
 select date('2022-08-21') d union all
 select date('2022-08-22') d union all
 select date('2022-08-23') d union all
 select date('2022-08-24') d union all
 select date('2022-08-25') d union all
 select date('2022-08-26') d union all
 select date('2022-08-27') d union all
 select date('2022-08-28') d union all
 select date('2022-08-29') d union all
 select date('2022-08-30') d union all
 select date('2022-08-31') d )
,vUserCnt as
(select 
 date(substring(u.CONFIRMED_AT,1,10)) dat
,count(*) cnt
from USERS u
where u.VALID_FLG='1'
and u.CONFIRMED_AT is not null
group by
 date(substring(u.CONFIRMED_AT,1,10)))
select 
 d.d REGIST_DATE
,case strftime('%w', d)
   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
,case when c.cnt is null then 0 else c.cnt end TOTAL
from vDat d
left join vUserCnt c
	on d.d = c.dat
order by 
d.d