SELECT 
 REGIST_DATE,
 CASE
  WHEN WK='0' THEN '日'
  WHEN WK='1' THEN '月'
  WHEN WK='2' THEN '火'
  WHEN WK='3' THEN '水'
  WHEN WK='4' THEN '木'
  WHEN WK='5' THEN '金'
  ELSE '土' 
 END AS WK,
 COUNT(REGIST_DATE) AS TOTAL
FROM 
(SELECT 
 DATE(CONFIRMED_AT) AS REGIST_DATE,
 strftime('%w', CONFIRMED_AT) AS WK
FROM 
 USERS
WHERE
 VALID_FLG ='1'
 AND CONFIRMED_AT IS NOT NULL )
GROUP BY REGIST_DATE,WK