WITH RECURSIVE
    cnt(x) AS (VALUES(julianday(replace('2022/08/01', '/', '-') ))
    UNION ALL
    SELECT
        x + 1
    FROM
        cnt
    WHERE
        x < julianday(replace('2022/08/31', '/', '-') ))

SELECT
    d.REGIST_DATE,
    d.WK,
    CASE 
        WHEN u.REGIST_CNT IS NULL THEN 0
        ELSE u.REGIST_CNT
    END AS TOTAL
FROM
(
    SELECT
        strftime('%Y-%m-%d', x) AS REGIST_DATE,
        CASE strftime('%w', x)
            WHEN '0' THEN '日'
            WHEN '1' THEN '月'
            WHEN '2' THEN '火'
            WHEN '3' THEN '水'
            WHEN '4' THEN '木'
            WHEN '5' THEN '金'
            WHEN '6' THEN '土'
        END AS WK
    FROM
        cnt
) AS d
LEFT JOIN
(
    SELECT
        CONFIRMED_DATE,
        COUNT(*) AS REGIST_CNT
    FROM
    (
        SELECT
            USER_CODE,
            strftime('%Y-%m-%d', CONFIRMED_AT) AS CONFIRMED_DATE
        FROM
            USERS
        WHERE
            VALID_FLG = '1'
        AND CONFIRMED_AT IS NOT NULL
    )
    GROUP BY
        CONFIRMED_DATE
)AS u
    ON
        d.REGIST_DATE = u.CONFIRMED_DATE