SELECT confirmed_date AS REGIST_DATE
     , CASE strftime('%w', confirmed_date)
       WHEN '0' THEN '日'
       WHEN '1' THEN '月'
       WHEN '2' THEN '火'
       WHEN '3' THEN '水'
       WHEN '4' THEN '木'
       WHEN '5' THEN '金'
       WHEN '6' THEN '土'
       END AS WK
     , COUNT(VALID_FLG = '1' OR NULL) AS TOTAL
FROM (
    SELECT *
        , DATE(CONFIRMED_AT) AS confirmed_date
    FROM USERS
  )
WHERE confirmed_date LIKE '2022-08-%'
GROUP BY confirmed_date