with m_date as (
    with RECURSIVE
        cnt(x) as (VALUES(julianday(replace('2022/08/01', '/', '-')))
        UNION ALL
        select x+1 from cnt where x < julianday(replace('2022/08/31', '/', '-') ))
    select
        date(x) as REGIST_DATE,
        substr('日月火水木金土', strftime('%w', x)+1,1) as WK
    from
        cnt
),
user_counts as (
    select
        date(CONFIRMED_AT) as REGIST_DATE,
        count(1) as TOTAL
    from
        USERS
    where
        date(CONFIRMED_AT) between "2022-08-01" and "2022-08-31"
        and VALID_FLG='1'
    group by
        1
)
select
    strftime('%Y-%m-%d', m.REGIST_DATE) as REGIST_DATE,
    m.WK,
    coalesce(u.TOTAL, 0) as TOTAL
from
    m_date as m
left join
    user_counts as u
    on m.REGIST_DATE = u.REGIST_DATE
order by
    m.REGIST_DATE