select
    substr(CONFIRMED_AT,1,10) as REGIST_DATE
    ,substr(CONFIRMED_AT,9,2) % 7 as aa
    ,case substr(CONFIRMED_AT,9,2) % 7
        when 1 then '月' 
        when 2 then '火' 
        when 3 then '水' 
        when 4 then '木' 
        when 5 then '金' 
        when 6 then '土'
        when 0 then '日' 
        else 'a'
    END as WK
    ,count(USER_CODE) as TOTAL
from USERS
where substr(CONFIRMED_AT,1,7) = '2022-08'
and VALID_FLG = 1
group by substr(CONFIRMED_AT,1,10)