WITH RECURSIVE
    cnt(x) AS (VALUES(julianday('2022-08-01')) 
    UNION ALL
    SELECT x+1 FROM cnt WHERE x < julianday('2022-08-31'))
SELECT DISTINCT
    strftime('%Y-%m-%d', x) AS REGIST_DATE
    , CASE
            WHEN strftime('%w', strftime('%Y-%m-%d', x)) = '0'
                THEN '日'
            WHEN strftime('%w', strftime('%Y-%m-%d', x)) = '1'
                THEN '月'
            WHEN strftime('%w', strftime('%Y-%m-%d', x)) = '2'
                THEN '火'
            WHEN strftime('%w', strftime('%Y-%m-%d', x)) = '3'
                THEN '水'
            WHEN strftime('%w', strftime('%Y-%m-%d', x)) = '4'
                THEN '木'
            WHEN strftime('%w', strftime('%Y-%m-%d', x)) = '5'
                THEN '金'
            WHEN strftime('%w', strftime('%Y-%m-%d', x)) = '6'
                THEN '土'
    END AS WK
    , CASE
        WHEN DATE(CONFIRMED_AT) IS NULL
            THEN 0
        ELSE
            COUNT() OVER (PARTITION BY strftime('%Y-%m-%d', x)) 
    END AS TOTAL
FROM cnt
    LEFT OUTER JOIN (
        SELECT
            *
        FROM
            USERS
        WHERE
            VALID_FLG='1'
    )
        ON (strftime('%Y-%m-%d', x)) = (DATE(CONFIRMED_AT))
ORDER BY 1;