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 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 calendar.calendar_date = total.calendar_date