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 strftime('%Y-%m-%d',calendar.calendar_date) as REGIST_DATE, 
case strftime('%w', calendar.calendar_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
, COALESCE(total.TOTAL,0) as TOTAL
FROM calendar
left join(
select
strftime('%Y-%m-%d',CONFIRMED_AT) as calendar_date
,COUNT(USER_CODE) as TOTAL
from
USERS
where
CONFIRMED_AT between '2022-08-01' and '2022-08-31'
group by strftime('%Y-%m-%d',CONFIRMED_AT)
)total
on strftime('%Y-%m-%d',calendar.calendar_date) = total.calendar_date
order by REGIST_DATE