with cal as (
    select date('2022-08-01') as day
    union all
    select date(day, '+1 days') from cal where day < date('2022-08-31')
),rec as (
    select
        date(confirmed_at) day,
        count(0) as total
    from
        USERS
    where
        VALID_FLG = '1'
    group by
        date(confirmed_at)
)
select
    REGIST_DATE,
    (case
        when WK = '0' then '日' 
        when WK = '1' then '月' 
        when WK = '2' then '火' 
        when WK = '3' then '水' 
        when WK = '4' then '木' 
        when WK = '5' then '金' 
        when WK = '6' then '土'
    end) as WK,
    TOTAL
from
(
select
    cal.day as REGIST_DATE,
    strftime('%w', cal.day) as WK,
    (case when rec.total is null then 0 else rec.total end) as TOTAL
from
    cal
left outer join
    rec
on
    cal.day = rec.day
) A
order by
REGIST_DATE asc