WITH RECURSIVE CALENDAR(REGIST_DATE) AS (
    SELECT
        DATE('2022-08-01') AS REGIST_DATE
    UNION ALL
    SELECT
        DATE(REGIST_DATE, '+1 day') AS REGIST_DATE
    FROM CALENDAR
    WHERE
        REGIST_DATE < DATE('2022-08-31')
)
SELECT
    CALENDAR.REGIST_DATE,
    CASE CAST(STRFTIME('%w', CALENDAR.REGIST_DATE) AS INTEGER)
    WHEN 0 THEN '日'
    WHEN 1 THEN '月'
    WHEN 2 THEN '火'
    WHEN 3 THEN '水'
    WHEN 4 THEN '木'
    WHEN 5 THEN '金'
    WHEN 6 THEN '土'
    END AS WK,
    COALESCE(USERS.TOTAL, 0) AS TOTAL
FROM
    CALENDAR
LEFT JOIN
    (
        SELECT
            DATE(CONFIRMED_AT) AS REGIST_DATE,
            COUNT(*) AS TOTAL
        FROM
            USERS
        WHERE
            VALID_FLG = 1
        GROUP BY
            REGIST_DATE
    ) AS
    USERS ON
    USERS.REGIST_DATE = CALENDAR.REGIST_DATE
ORDER BY
    CALENDAR.REGIST_DATE ASC