WITH tmp AS(
SELECT *, substr(CONFIRMED_AT, 0, 11) AS day
FROM USERS
WHERE CONFIRMED_AT IS NOT NULL
AND   VALID_FLG = '1'
)
, 
days(i, d) AS (
    SELECT 1, format('2022-08-%02d', 1)
    UNION ALL
    SELECT i+1, format('2022-08-%02d', i+1) FROM days
    WHERE i < 31
)

SELECT
    d AS REGIST_DATE
    ,CASE strftime('%w', d)
        when '0' then '日'
        when '1' then '月'
        when '2' then '火'
        when '3' then '水'
        when '4' then '木'
        when '5' then '金'
        when '6' then '土'
    END AS WK
    ,COUNT(USER_CODE) AS TOTAL
FROM days
LEFT OUTER JOIN tmp ON days.d = tmp.day
GROUP BY d
ORDER BY d