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