WITH DATE_TBL AS ( 
    -- 2022年08月のカレンダー仮想テーブルを定義する
    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
        DATE_TBL 
    WHERE
        CLDATE < '2022-08-31'
) 
SELECT
    CLDATE AS REGIST_DATE
    , WEEK AS WK
    , COUNT(USER_CODE) AS TOTAL 
FROM
    DATE_TBL 
    LEFT OUTER JOIN USERS 
        ON DATE (USERS.CONFIRMED_AT) = DATE_TBL.CLDATE 
        AND VALID_FLG = '1' 
GROUP BY
    CLDATE 
ORDER BY
    CLDATE ASC;