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,
    substr('日月火水木金土', strftime('%w', D.register_date) + 1, 1) 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
;