with serial as (
    select '01' as N
    union select '02' as N
    union select '03' as N
    union select '04' as N
    union select '05' as N
    union select '06' as N
    union select '07' as N
    union select '08' as N
    union select '09' as N
    union select '10' as N
    union select '11' as N
    union select '12' as N
    union select '13' as N
    union select '14' as N
    union select '15' as N
    union select '16' as N
    union select '17' as N
    union select '18' as N
    union select '19' as N
    union select '20' as N
    union select '21' as N
    union select '22' as N
    union select '23' as N
    union select '24' as N
    union select '25' as N
    union select '26' as N
    union select '27' as N
    union select '28' as N
    union select '29' as N
    union select '30' as N
    union select '31' as N
    --SQLiteの再起＆日付生成がうまくできず諦めた（言い訳）
    ), 
    
    user_list as (
    select strftime('%Y-%m-%d', CONFIRMED_AT) as REGIST_DATE,
         case strftime('%w', CONFIRMED_AT)
                when '0' then '日'
                when '1' then '月'
                when '2' then '火'
                when '3' then '水'
                when '4' then '木'
                when '5' then '金'
                when '6' then '土'
                else '不明'
            end as WK,
        USER_CODE
    from USERS
    where VALID_FLG='1'
    ),
    
    --集約
    summary as (select REGIST_DATE,
           WK,
            COUNT(USER_CODE) as TOTAL
    from user_list
    where REGIST_DATE BETWEEN '2022-08-01' and '2022-08-31'
    group by REGIST_DATE
    ),

    --結合キー（日の整数）を持たせる
    summary_for_join as (
    select strftime('%d',REGIST_DATE) as date_key,
            *
    from summary
    ),

    --完成形の手前
    temp as (
    select date('2022-08-' || N) as REGIST_DATE,
            WK,
            ifnull(TOTAL,0) as TOTAL
    from serial s1 left join summary_for_join s2
        on s1.N = s2.date_key
    )

--これはひどい回答だなあ みつを
select REGIST_DATE,
        case strftime('%w', REGIST_DATE)
        when '0' then '日'
        when '1' then '月'
        when '2' then '火'
        when '3' then '水'
        when '4' then '木'
        when '5' then '金'
        when '6' then '土'
        else '不明' end as WK,
        TOTAL
from temp
order by 1
;