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_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 '土'
    end as WK,
  ifnull(z.TOTAL2,'0') AS TOTAL
FROM calendar 
left OUTER JOIN 
(select 
date(CONFIRMED_AT) AS REGIST2_DATE,
count(*) AS TOTAL2
from USERS AS a
where a.CONFIRMED_AT >= "2022-08-01" AND a.CONFIRMED_AT <= "2022-08-31" AND a.CONFIRMED_AT not null and a.VALID_FLG='1'
group by date(CONFIRMED_AT)
) as z
ON calendar_date =z. REGIST2_DATE
order by calendar.calendar_date ;