with recursive
daterange(d) as (
    select date('2022-08-01')
    union all
    select date(d, '+1 days') from daterange
    where d < date('2022-08-31')
),
t as (
    select strftime('%Y-%m-%d', confirmed_at) as d, user_code
    from users
    where datetime('2022-08-01 00:00') <= confirmed_at
        and confirmed_at < datetime('2022-09-01 00:00')
        and valid_flg = 1
)
select d as REGIST_DATE,
    substr('日月火水木金土', strftime("%w", date(d)) + 1, 1) as WK,
    count(user_code) as TOTAL
from daterange
    left join t
        using(d)
group by d
order by REGIST_DATE