WITH temp AS(
    SELECT
        DATE(CONFIRMED_AT) AS REGIST_DATE
        , strftime('%w', DATE(CONFIRMED_AT)) AS WK_number
        , COUNT() OVER (PARTITION BY DATE(CONFIRMED_AT)) AS TOTAL
    FROM
        USERS
    WHERE DATE(CONFIRMED_AT) BETWEEN '2022-08-01' AND '2022-08-31'
        AND VALID_FLG='1'
)
SELECT DISTINCT
        REGIST_DATE
        , CASE
            WHEN WK_number = '0'
                THEN '日'
            WHEN WK_number = '1'
                THEN '月'
            WHEN WK_number = '2'
                THEN '火'
            WHEN WK_number = '3'
                THEN '水'
            WHEN WK_number = '4'
                THEN '木'
            WHEN WK_number = '5'
                THEN '金'
            WHEN WK_number = '6'
                THEN '土'
        END AS WK
        , TOTAL
FROM
    temp
ORDER BY 1;