WITH SEQ_TBL AS (
    SELECT 1 AS SEQ_NO
    UNION ALL 
    SELECT SEQ_NO + 1 
    FROM SEQ_TBL 
    WHERE SEQ_NO < 31
),
CALENDER AS (
    SELECT
        CASE 
            WHEN SEQ_NO < 10 THEN '2022-08-0' || CAST(SEQ_NO AS VARCHAR)
            ELSE '2022-08-' || CAST(SEQ_NO AS VARCHAR)
        END AS CA_DATE
        ,CASE
            WHEN MOD(SEQ_NO, 7) = 0 THEN '日'
            WHEN MOD(SEQ_NO, 7) = 1 THEN '月'
            WHEN MOD(SEQ_NO, 7) = 2 THEN '火'
            WHEN MOD(SEQ_NO, 7) = 3 THEN '水'
            WHEN MOD(SEQ_NO, 7) = 4 THEN '木'
            WHEN MOD(SEQ_NO, 7) = 5 THEN '金'
            WHEN MOD(SEQ_NO, 7) = 6 THEN '土'
        END AS WK
    FROM SEQ_TBL
),
TARGET_USERS AS (
    SELECT
        REGIST_DATE
        ,COUNT(USER_CODE) AS TOTAL
    FROM (
        SELECT
            USER_CODE
            ,SUBSTRING(CONFIRMED_AT, 1, 10) AS REGIST_DATE
        FROM USERS
        WHERE
            VALID_FLG = '1' AND
            SUBSTRING(CONFIRMED_AT, 1, 7) = '2022-08'
    )
    GROUP BY REGIST_DATE
)
SELECT 
    ca.CA_DATE AS REGIST_DATE
    ,ca.WK AS WK
    ,COALESCE(tu.TOTAL, 0) AS TOTAL
FROM CALENDER ca
LEFT OUTER JOIN TARGET_USERS tu
    ON ca.CA_DATE = tu.REGIST_DATE;