with
	days as (
		select
		    date('2022-08-01') as day
		union all
		select
		    date(day, '+1 days') as day
		from
		    days
		where
		    days.day < date('2022-08-31')
    )
    ,u as (
        select
            date(confirmed_at) as day
            ,count(user_code) as user_num
        from
            users
        where
            valid_flg = '1'
        group by
            1
    )
    ,j as (
        select
            days.day
            ,case
                when strftime('%w', days.day) = '0' then '日'
                when strftime('%w', days.day) = '1' then '月'
                when strftime('%w', days.day) = '2' then '火'
                when strftime('%w', days.day) = '3' then '水'
                when strftime('%w', days.day) = '4' then '木'
                when strftime('%w', days.day) = '5' then '金'
                when strftime('%w', days.day) = '6' then '土'
                end as youbi
            ,u.user_num
        from
            days
        left join
            u
            on
                days.day = u.day
    )

select
    day as REGIST_DATE
    ,youbi as WK
    ,case
        when user_num is null then 0
        else user_num
        end as 'TOTAL'
from
    j
order by
    1