WITH dates 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
        dates 
    WHERE
        CLDATE < '2022-08-31'
)
, t1 AS (
    SELECT
        DATE(CONFIRMED_AT) as REGIST_DATE,
        USER_CODE
    FROM users
    WHERE VALID_FLG = '1'
)
, t2 AS (
    SELECT
        dates.CLDATE AS REGIST_DATE,
        dates.WEEK AS WK,
        t1.USER_CODE
    FROM dates
    LEFT JOIN t1 ON dates.CLDATE = t1.REGIST_DATE
)
SELECT
    REGIST_DATE,
    WK,
    COUNT(USER_CODE) AS TOTAL
FROM t2
GROUP BY
    REGIST_DATE,
    WK
ORDER BY 1