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, COALESCE(total.TOTAL,0) 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