with RECURSIVE t as (
    select '2022-08-01' as REGIST_DATE, substr('日月火水木金土', strftime('%w', '2022-08-01')+1, 1) as WK
    union all
    select date(REGIST_DATE,'+1 days'), substr('日月火水木金土', strftime('%w', date(REGIST_DATE, '+1 days'))+1, 1)
    from t
    where REGIST_DATE < '2022-08-31'
)
select REGIST_DATE,WK, count(USER_CODE) as TOTAL
from t left outer join USERS as u on REGIST_DATE=date(u.CONFIRMED_AT) and u.VALID_FLG=1
group by REGIST_DATE
order by REGIST_DATE
;