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')
)

,user_list as (
select
    date(confirmed_at) as CONF_DAY
    -- ,confirmed_at
    ,count(*) as TOTAL
from
    USERS
where
    VALID_FLG='1'
group by 1
)

-- select * from user_list;

select
    c.calendar_date as REGIST_DATE
    ,case
        when strftime('%w', c.calendar_date)='0' then '日'
        when strftime('%w', c.calendar_date)='1' then '月'
        when strftime('%w', c.calendar_date)='2' then '火'
        when strftime('%w', c.calendar_date)='3' then '水'
        when strftime('%w', c.calendar_date)='4' then '木'
        when strftime('%w', c.calendar_date)='5' then '金'
        when strftime('%w', c.calendar_date)='6' then '土'
    end as WK
    ,coalesce(u.TOTAL, 0) as TOTAL
from
    calendar c
    left outer join
        user_list u
        on
            c.calendar_date=u.conf_day
order by 1,2,3
;