WITH T1 AS(
    SELECT
        '2022-08-01' AS REGIST_DATE
    UNION ALL
    SELECT
        DATE(REGIST_DATE, '+1 days')
    FROM
        T1
    WHERE
        REGIST_DATE < '2022-08-31'
),
T2 AS(
    SELECT
        DATE(CONFIRMED_AT) AS REGIST_DATE,
        COUNT(USER_CODE) AS TOTAL
    FROM
        USERS
    WHERE
        CONFIRMED_AT IS NOT NULL
        AND REGIST_DATE BETWEEN '2022-08-01' AND '2022-08-31'
        AND VALID_FLG = '1'
    GROUP BY
        REGIST_DATE
)
SELECT
    T1.REGIST_DATE,
    CASE STRFTIME('%w', T1.REGIST_DATE)
        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,
    IFNULL(TOTAL, 0) AS TOTAL
FROM
    T1
LEFT OUTER JOIN
    T2 ON T2.REGIST_DATE = T1.REGIST_DATE