WITH CALENDAR AS (
    SELECT 
        '2022-08-01' AS HIDUKE,
        STRFTIME('%w','2022-08-01') AS WD_NUM,
        SUBSTRING('日月火水木金土',STRFTIME('%w','2022-08-01')+1,1) AS WK
    UNION ALL
    SELECT
        DATE(HIDUKE,'+1 day'),
        STRFTIME('%w',DATE(HIDUKE,'+1 day')),
        SUBSTRING('日月火水木金土',STRFTIME('%w',DATE(HIDUKE,'+1 day'))+1,1)
    FROM
        CALENDAR
    WHERE
        HIDUKE < '2022-08-31'
    )
SELECT
    HIDUKE AS REGIST_DATE,
    WK,
    IFNULL(CNT, 0) AS TOTAL
FROM
    CALENDAR
    LEFT OUTER JOIN (
        SELECT
            IFNULL(DATE(CONFIRMED_AT),'2999-01-01') AS TOROKUBI,
            COUNT(USER_CODE) AS CNT
        FROM
            USERS
        WHERE
            VALID_FLG='1'
            AND TOROKUBI BETWEEN '2022-08-01' AND '2022-08-31'
        GROUP BY
            TOROKUBI
        ) AS SUB
        ON SUB.TOROKUBI = CALENDAR.HIDUKE
    ;