with recursive calendar(REGIST_DATE) as (
    select 
        date('2022-08-01') as REGIST_DATE 
    union all 
    select
        date(REGIST_DATE,'+1 day') as REGIST_DATE 
    from calendar 
    where 
        regist_date < date('2022-08-31')
),tbl as(
    select 
        substr(confirmed_at,1,10) as REGIST_DATE,
        count(*) as cnt
    from users 
    where valid_flg='1' and substr(confirmed_at,1,7)='2022-08' 
    group by regist_date 
    order by regist_date
)
select 
    calendar.REGIST_DATE,
    substr('日月火水木金土', strftime("%w", calendar.REGIST_DATE, 'localtime')+1, 1) as WK ,
    ifnull(cnt,0)  as TOTAL 
from calendar 
left join tbl 
on calendar.regist_date=tbl.REGIST_DATE