select 
    CALENDER.REGIST_DATE as REGIST_DATE,
    case
        when strftime('%w', CALENDER.REGIST_DATE) = '0' then '日'
        when strftime('%w', CALENDER.REGIST_DATE) = '1' then '月'
        when strftime('%w', CALENDER.REGIST_DATE) = '2' then '火'
        when strftime('%w', CALENDER.REGIST_DATE) = '3' then '水'
        when strftime('%w', CALENDER.REGIST_DATE) = '4' then '木'
        when strftime('%w', CALENDER.REGIST_DATE) = '5' then '金'
        when strftime('%w', CALENDER.REGIST_DATE) = '6' then '土'
    else NULL end as WK,
    count(USER_CODE) as TOTAL
from
    (select
        date(CONFIRMED_AT) as REGIST_DATE
     from
        USERS
     where
        CONFIRMED_AT is not null
     and
        date(CONFIRMED_AT) between '2022-08-01' and '2022-08-31'
        
    ) CALENDER
left join
    USERS U_TOTAL on CALENDER.REGIST_DATE = U_TOTAL.date(CONFIRMED_AT)
where
    VALID_FLG = '1'
group by
    REGIST_DATE ,WK
;