WITH days AS (
    SELECT '2022-08-01' AS DAYS
    UNION ALL
    SELECT DATE(DAYS, '+1 days') FROM days WHERE DAYS < '2022-08-31'
), wk AS (
    SELECT DAYS,
    SUBSTR('日月火水木金土', STRFTIME("%w", SUBSTR(DAYS, 0, 11)) + 1, 1) as WK
    FROM days
), data AS (
    SELECT
        SUBSTR(CONFIRMED_AT, 0, 11) AS REGIST_DATE
    FROM USERS
    WHERE CONFIRMED_AT LIKE '2022-08%'
    AND VALID_FLG = '1'
    ORDER BY REGIST_DATE
)
SELECT
    DAYS AS REGIST_DATE,
    WK,
    COUNT(REGIST_DATE) AS TOTAL
FROM wk LEFT OUTER JOIN data ON wk.DAYS = data.REGIST_DATE
GROUP BY DAYS
ORDER BY DAYS ASC