WITH RECURSIVE TMP(REGIST_DATE) AS(
SELECT '2022-08-01' AS REGIST_DATE
UNION ALL
SELECT DATE(REGIST_DATE, '+1 DAYS')
FROM TMP
WHERE DATE(REGIST_DATE, '+1 DAYS') <= '2022-08-31'
)

, REGIST_COUNT AS (
SELECT
    STRFTIME('%Y-%m-%d',CONFIRMED_AT) AS REGIST_DATE
    
    ,COUNT(CONFIRMED_AT) AS TOTAL
FROM USERS
WHERE VALID_FLG = '1' AND CONFIRMED_AT IS NOT NULL AND CONFIRMED_AT BETWEEN '2022-08-01' AND '2022-08-31'
GROUP BY
    STRFTIME('%Y-%m-%d', CONFIRMED_AT)
ORDER BY REGIST_DATE
)

SELECT
    REGIST_DATE
    ,CASE STRFTIME('%w', REGIST_DATE)
        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(TOTAL, 0) AS TOTAL
FROM TMP LEFT JOIN REGIST_COUNT USING(REGIST_DATE)