with TMP as (
    select
        '2022-08-01' as YYYYMMDD
    union
    select
        date(YYYYMMDD, '+1 days')
    from TMP
    where YYYYMMDD < '2022-08-31'
), CALENDAR as (
    select
        YYYYMMDD
        , substr('日月火水木金土', strftime('%w', YYYYMMDD) + 1, 1) as WEEK
    from TMP
)
select
    YYYYMMDD as REGIST_DATE
    , WEEK as WK
    , count(CONFIRMED_AT) as TOTAL
from CALENDAR
left outer join USERS
    on YYYYMMDD = substr(CONFIRMED_AT, 1, 10) and VALID_FLG='1'
group by YYYYMMDD
order by YYYYMMDD