with date_tbl as (
    select
        '2022-08-01' as cldate
        , strftime('%w','2022-08-01') as wkno
        , substr('日月火水木金土',strftime('%w','2022-08-01')+1,1) as week
    union all
        select
            date(cldate,'+1 days')
            , strftime('%w',date(cldate,'+1 days'))
            , substr(
                    '日月火水木金土'
                    ,strftime('%w',date(cldate,'+1 days'))+1
                    ,1
                    )
    from date_tbl
    where cldate<'2022-08-31'
)
select 
    cldate
    , week as WK
    , count(user_code) as TOTAL
from date_tbl
left join users on date(users.confirmed_at)=date_tbl.cldate
                and valid_flg='1'
group by cldate
order by cldate asc;