WITH
_calendar AS
(
SELECT
    '2022-08-01' AS CLDATE
    , STRFTIME('%w', '2022-08-01') AS WKNO
    -- 曜日の番号を取得し該当する曜日を取得する
    , SUBSTR('日月火水木金土', STRFTIME('%w', '2022-08-01') + 1, 1) AS WEEK 
UNION ALL 
SELECT
    DATE (CLDATE, '+1 days')
    , STRFTIME('%w', DATE (CLDATE, '+1 days'))
    , SUBSTR( 
        '日月火水木金土'
        , STRFTIME('%w', DATE (CLDATE, '+1 days')) + 1
        , 1
                ) 
FROM
        _calendar 
WHERE
        CLDATE < '2022-08-31'
)

SELECT
_calendar.CLDATE AS REGIST_DATE,
_calendar.WEEK  AS WK,
COUNT(u.USER_CODE) AS  TOTAL
FROM
_calendar
LEFT JOIN USERS u ON _calendar.CLDATE  = DATE(u.CONFIRMED_AT) AND u.VALID_FLG = 1
GROUP BY REGIST_DATE
ORDER BY REGIST_DATE ASC