WITH AUGUST AS (
    select
        date('2022-08-01') as REGIST_DATE,
        case strftime("%w", date('2022-08-01')) 
            when '0' then '日'
            when '1' then '月'
            when '2' then '火'
            when '3' then '水'
            when '4' then '木'
            when '5' then '金'
            else '土'
        end as WK
    union all
    select
        date(REGIST_DATE, "+1 day"),
        case strftime("%w", date(REGIST_DATE, "+1 day")) 
            when '0' then '日'
            when '1' then '月'
            when '2' then '火'
            when '3' then '水'
            when '4' then '木'
            when '5' then '金'
            else '土'
        end
    from
        AUGUST
    where
        REGIST_DATE < date('2022-08-31')
)

select
    A.REGIST_DATE,
    A.WK,
    case 
        when count(user_code) is null then 0
        else count(user_code)
    end as TOTAL
from
    (
    select
        user_code,
        date(CONFIRMED_AT) as REGIST_DATE
    from
        USERS
    where
        VALID_FLG == '1'
    ) NU
right outer join
    AUGUST as A
    on A.REGIST_DATE = NU.REGIST_DATE
group by
    A.REGIST_DATE
order by
    A.REGIST_DATE;