WITH RECURSIVE calendar(calendar_date) AS (
    SELECT
        -- ここが始まりの日付
        DATE('2022-08-01') AS calendar_date
    UNION ALL
    -- UNION ALL 以下が再帰処理部分
    SELECT
        DATE(calendar_date, '+1 day') AS calendar_date
    FROM calendar
    WHERE
        -- ここが終わりの日付
        calendar_date < DATE('2022-08-31')
)

-- ここが実際のSELECT文
select CALENTAR.REGIST_DATE,
    CALENTAR.WK,
    ifnull(CONFIRMED_USER.TOTAL,0) as TOTAL
from (
    SELECT calendar_date as REGIST_DATE,
            case strftime('%w', calendar_date)
                when '0' then '日'
                when '1' then '月'
                when '2' then '火'
                when '3' then '水'
                when '4' then '木'
                when '5' then '金'
                when '6' then '土'
                else 'undefined'
             end as WK
    FROM calendar
) CALENTAR
left join
(
    select CONFIRMED_DATE,count(USER_CODE) as TOTAL
    from (
        select date(CONFIRMED_AT) as CONFIRMED_DATE, USER_CODE
        from USERS
        where VALID_FLG='1'
    )
    group by CONFIRMED_DATE
    having date('2022-08-01') <= CONFIRMED_DATE
    and date('2022-09-01') > CONFIRMED_DATE
) CONFIRMED_USER
on CALENTAR.REGIST_DATE = CONFIRMED_USER.CONFIRMED_DATE
order by CALENTAR.REGIST_DATE