WITH RECURSIVE c AS (
    SELECT
        DATE('2022-08-01') AS d
    UNION ALL
        SELECT
            DATE(d, '+1 day') AS d
        FROM c
    WHERE
        d < DATE('2022-08-31')
)

SELECT
    c.d AS REGIST_DATE
    , SUBSTR('日月火水木金土', strftime('%w', c.d) + 1, 1) AS WK
    , IFNULL(t.c, 0) AS TOTAL
FROM
    c
LEFT JOIN
    (
        SELECT
            DATE(CONFIRMED_AT) AS d
            , COUNT() AS c
        FROM
            USERS
        WHERE
            VALID_FLG = 1
        GROUP BY d
    ) AS t
ON
    c.d = t.d
;