with
    d1(day) as
    (
        select 1
        union all
        select day + 1
        from d1
        where day < 31
            
    ),
    d2(date) as
    (
        select '2022-08-' || substr('0' || day, -2, 2)
        from d1
    ),
    u1 as
    (
        select DATE(CONFIRMED_AT) as c_date
        from USERS
        where
            CONFIRMED_AT like '2022-08%'
            and VALID_FLG = 1
    ),
    u2 as
    (
        select date
        from d2
        where not exists
            (
                select *
                from u1
                where date = c_date
            )
    )
select
    c_date as REGIST_DATE,
    case strftime('%w', c_date)
        when '0' then '日'
        when '1' then '月'
        when '2' then '火'
        when '3' then '水'
        when '4' then '木'
        when '5' then '金'
        when '6' then '土'
    end as WK,
    TOTAL
from
    (
        select c_date, count(1) as TOTAL
        from u1
        group by c_date
        union
        select date as c_date, 0 as TOTAL
        from u2
    )
;