WITH RECURSIVE calendar(calendar_date) AS (
    SELECT
        DATE('2022-08-01') AS calendar_date
    UNION ALL
    SELECT
        DATE(calendar_date, '+1 day') AS calendar_date
    FROM calendar
    WHERE
        calendar_date < DATE('2022-08-31')
)
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.VALID_FLG='1'
group by date(CONFIRMED_AT)
) as z
ON calendar_date =z. REGIST2_DATE
order by calendar.calendar_date ;