with tmp as (
select date(substring(CONFIRMED_AT, 1,10)) as REGIST_DATE, 
    case strftime('%w', date(substring(CONFIRMED_AT, 1,10)))
        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
from users
)
select REGIST_DATE, WK, count(*) as TOTAL from tmp
where REGIST_DATE is not null
    and  '2022-08-01' <= REGIST_DATE
    and REGIST_DATE <= '2022-08-31'
group by REGIST_DATE, WK