WITH CALENDAR AS (
SELECT
    DATE('2022-08-01') CAL_DATE,
    0 CNT
UNION ALL
SELECT
    DATE(CAL_DATE, '+1 day') CAL_DATE,
    0 CNT
FROM
    CALENDAR
WHERE
    CAL_DATE < DATE('2022-08-31')
)

SELECT
    CAL_DATE REGIST_DATE,
    CASE STRFTIME('%w', CAL_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,
    SUM(CNT) TOTAL
FROM (
SELECT
    *
FROM
    CALENDAR
UNION ALL
SELECT
    DATE(CONFIRMED_AT) CAL_DATE,
    1 CNT
FROM
    USERS
WHERE
    CONFIRMED_AT IS NOT NULL
    AND
    DATE('2022-08-01') <= CONFIRMED_AT
    AND
    CONFIRMED_AT < DATE('2022-09-01')
    AND
    VALID_FLG='1'
)
GROUP BY
    CAL_DATE
ORDER BY
    REGIST_DATE
;