with a as(
 select date('2022-08-01') as day
 union 
 select date(day, '+1 day')
 from a
 where day < date('2022-08-31')
 )
select
a.day as REGIST_DATE,
case strftime('%w', a.day)
        when '0' then '日'
        when '1' then '月'
        when '2' then '火'
        when '3' then '水'
        when '4' then '木'
        when '5' then '金'
        when '6' then '土'
    end as WK,
count(USERS.USER_CODE) as TOTAL
from a
left join USERS
on  USERS.VALID_FLG = '1' and a.day = date(USERS.CONFIRMED_AT) 
group by a.day
order by a.day