SELECT 
    REGIST_DATE
    , WK
    , COALESCE(TOTAL,0) AS TOTAL
FROM
(
SELECT
        '2022-08-' || IIF(N >= 10,'','0') || CAST(N AS TEXT) AS REGIST_DATE
        , SUBSTR('日月火水木金土',(N % 7) + 1,1) WK
    FROM
        (
            SELECT
                    ROW_NUMBER() OVER () AS N
                FROM
                    (VALUES(1),(2),(3),(4),(5),(6)) N1
                    , (VALUES(1),(2),(3),(4),(5),(6)) N2
        )
    WHERE
        N <= 31 
) A
LEFT OUTER JOIN ( 


SELECT
    strftime('%Y-%m-%d', datetime(CONFIRMED_AT)) REGIST_DATE
    , COUNT(*) TOTAL
FROM
    USERS
WHERE
    VALID_FLG = '1'
    AND datetime(CONFIRMED_AT) >= datetime('2022-08-01T00:00:00') 
    AND datetime(CONFIRMED_AT) < datetime('2022-09-01T00:00:00') 
GROUP BY
    strftime('%Y-%m-%d', datetime(CONFIRMED_AT))
ORDER BY
    REGIST_DATE
) B
USING
    (REGIST_DATE)