WITH CLD AS(
    SELECT
        DATE('2022-08-01') AS REGIST_DATE
    UNION ALL
    SELECT
        DATE(REGIST_DATE, '+1 DAY') AS REGIST_DATE
    FROM CLD
    WHERE
        REGIST_DATE < DATE('2022-09-01')

    )
SELECT 
    REGIST_DATE --日付
    ,CASE WHEN strftime('%w', REGIST_DATE) = '0' THEN '日' 
            WHEN strftime('%w', REGIST_DATE) = '1' THEN '月'
            WHEN strftime('%w', REGIST_DATE) = '2' THEN '火'
            WHEN strftime('%w', REGIST_DATE) = '3' THEN '水'
            WHEN strftime('%w', REGIST_DATE) = '4' THEN '木'
            WHEN strftime('%w', REGIST_DATE) = '5' THEN '金'
            WHEN strftime('%w', REGIST_DATE) = '6' THEN '土'
            ELSE NULL END AS WK --曜日
    ,IFNULL(SUM(USERS.VALID_FLG), '0') AS TOTAL--日毎の登録人数の合計
FROM CLD
    LEFT OUTER JOIN
        USERS
    ON
        REGIST_DATE = DATE(USERS.'CONFIRMED_AT')
GROUP BY REGIST_DATE
ORDER BY
    REGIST_DATE --REGIST_DATEの昇順