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')
),
tr_user AS (
    SELECT
        STRFTIME('%Y-%m-%d',  CONFIRMED_AT) AS REGIST_DATE_tmp,
        count(0) AS TOTAL
    From
        USERS
    Where
        VALID_FLG = 1
    group by
        REGIST_DATE_tmp
),
tmp as(
select 
    *
from
    calendar
left join
   (select
        *
    from
        tr_user)
on
    calendar_DATE=REGIST_DATE_tmp
order by
    calendar_DATE
)
select
    calendar_date AS REGIST_DATE,
    substr('日月火水木金土', strftime("%w",calendar_date)+1, 1) as WK,
    ifnull(TOTAL,0) as TOTAL
    from tmp