with recursive dates as (select 
'2022-08-01' as d
union all
select date(d, '+1 day')
from dates
where d < '2022-08-31'
)
, u as (select count() as t, date(confirmed_at) as c
from  users
where valid_flg = 1
group by date(confirmed_at)
)
select d as REGIST_DATE
, substr('日月火水木金土', strftime('%w', d)+1, 1) as WK
, ifnull(t, 0) as TOTAL
from dates left join u on dates.d = u.c