WITH RECURSIVE calendar(calendar_date, calendar_w) AS (
    SELECT
        -- ここが始まりの日付
        DATE('2022-08-01') AS calendar_date
        ,case strftime('%w', '2022-08-01') 
            when '0' then '日'
            when '1' then '月'
            when '2' then '火'
            when '3' then '水'
            when '4' then '木'
            when '5' then '金'
            when '6' then '土'
        end AS calendar_w
    UNION ALL
    -- UNION ALL 以下が再帰処理部分
    SELECT
        DATE(calendar_date, '+1 day') AS calendar_date
        ,case strftime('%w', DATE(calendar_date, '+1 day'))
            when '0' then '日'
            when '1' then '月'
            when '2' then '火'
            when '3' then '水'
            when '4' then '木'
            when '5' then '金'
            when '6' then '土'
        end AS calendar_w
    FROM calendar
    WHERE
        -- ここが終わりの日付
        calendar_date < DATE('2022-08-31')
)

select calendar_date REGIST_DATE,calendar_w WK, count(USER_CODE) TOTAL
from calendar
left join USERS on date(CONFIRMED_AT) = calendar_date and VALID_FLG='1'
group by calendar_date,calendar_w