WITH DATE_TBL as (
    -- 2022年08月のカレンダー仮想テーブルを定義する
    select 
        '2022-08-01' as CLDATE, 
        STRFTIME('%w', '2022-08-01') as WKNO,
        SUBSTR('日月火水木金土', STRFTIME('%w', '2022-08-01') +1, 1) as WEEK
    union all
    select 
        DATE(CLDATE,'+1 days'),
        STRFTIME('%w', DATE(CLDATE,'+1 days')),
        SUBSTR('日月火水木金土', STRFTIME('%w', DATE(CLDATE,'+1 days')) +1, 1)
    from DATE_TBL
    where CLDATE < '2022-08-31'
)

select CLDATE as REGIST_DATE, WEEK as WK, count(USER_CODE) as TOTAL
from DATE_TBL
left outer join USERS
on DATE(USERS.CONFIRMED_AT)=DATE_TBL.CLDATE
where VALID_FLG='1'
group by REGIST_DATE;