WITH RECURSIVE DAYS AS(
    SELECT
        '2022-08-01' AS DAY
    UNION ALL
        SELECT
            DATE(DAY, '+1 days')
        FROM
            DAYS
        WHERE
            DAY < '2022-08-31'
)
SELECT
    DAY AS REGIST_DATE,
    CASE STRFTIME('%w', DAY)
        WHEN '0' THEN '日'
        WHEN '1' THEN '月'
        WHEN '2' THEN '火'
        WHEN '3' THEN '水'
        WHEN '4' THEN '木'
        WHEN '5' THEN '金'
        WHEN '6' THEN '土'
        ELSE NULL
    END AS WK,
    SUM(CASE VALID_FLG WHEN 1 THEN 1 ELSE 0 END) AS TOTAL
FROM
    DAYS
LEFT JOIN
    USERS
ON
    DAYS.DAY = DATE(USERS.CONFIRMED_AT)
GROUP BY
    DAY
ORDER BY
    DAY