with dates as (
    select date('2022-08-01') as register_date
    union all
    select date(register_date, '+1 day')
    from dates
    where date(register_date, '+1 day') <= date('2022-08-31')
)
select
    D.register_date as REGIST_DATE,
    case strftime('%w', D.register_date)
        when '0' then '日'
        when '1' then '月'
        when '2' then '火'
        when '3' then '水'
        when '4' then '木'
        when '5' then '金'
        when '6' then '土'
    end as WK,
    sum(case when U.valid_flg = '1' then 1 else 0 end) as TOTAL
from
    dates D
    left outer join users U
    on date(U.confirmed_at) = D.register_date
group by
    D.register_date
;