WITH tmp AS(
SELECT
    *
    , CASE WHEN VALID_FLG = '1' THEN 1 ELSE 0 END AS count
FROM
    USERS
WHERE
    STRFTIME('%Y-%m',CONFIRMED_AT) = '2022-08'
)
SELECT
    STRFTIME('%Y-%m-%d',CONFIRMED_AT) AS REGIST_DATE
    , CASE STRFTIME('%w',CONFIRMED_AT)
        WHEN '1' THEN '月'
        WHEN '2' THEN '火'
        WHEN '3' THEN '水'
        WHEN '4' THEN '木'
        WHEN '5' THEN '金'
        WHEN '6' THEN '土'
        WHEN '0' THEN '日'
        ELSE null END
    AS WK
    ,sum(count) AS TOTAL
FROM
    tmp
GROUP BY
    REGIST_DATE
;