WITH RECURSIVE CAL(date) AS (
    SELECT
        DATE('2022-08-01') AS date
    UNION ALL
    SELECT
        DATE(date, '+1 day') AS date
    FROM CAL
    WHERE
        date < DATE('2022-09-01')
)

SELECT
    CAL.date REGIST_DATE,
    substr('日月火水木金土', strftime("%w", CAL.date)+1, 1) WK,
    IFNULL(S.TOTAL, 0)
FROM
    CAL
LEFT JOIN (
    SELECT
        DATE(CONFIRMED_AT) REGIST_DATE,
        COUNT(*) TOTAL
    FROM
        USERS
    WHERE
        CONFIRMED_AT IS NOT NULL
        AND
        strftime("%m", CONFIRMED_AT) = '08'
        AND
        VALID_FLG = '1'
    GROUP BY
        DATE(CONFIRMED_AT)
) S
ON
S.REGIST_DATE = CAL.date