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-09-01')
), temp as (
select
    date(CONFIRMED_AT) as REGIST_DATE,
    case when strftime('%w',date(CONFIRMED_AT))='1' then '月'
         when strftime('%w',date(CONFIRMED_AT))='2' then '火'
         when strftime('%w',date(CONFIRMED_AT))='3' then '水'
         when strftime('%w',date(CONFIRMED_AT))='4' then '木'
         when strftime('%w',date(CONFIRMED_AT))='5' then '金'
         when strftime('%w',date(CONFIRMED_AT))='6' then '土'
         else '日' end as WK
    
from
    USERS
where
    substr(REGIST_DATE,1,7)='2022-08'
    and
    VALID_FLG='1'
), temp2 as (
select
    REGIST_DATE,WK,count(*) as TOTAL
from
    temp
group by
    REGIST_DATE
)
select
calendar_date as REGIST_DATE,WK,coalesce(TOTAL,0) as TOTAL
from
    calendar t1
    left outer join
    temp2 t2
    on t1.calendar_date=t2.REGIST_DATE
order by
    calendar_date