SELECT
    date(CONFIRMED_AT) AS REGIST_DATE
    , (
    case strftime('%w', CONFIRMED_AT)
         when '0' then '日'
         when '1' then '月'
         when '2' then '火'
       when '3' then '水'
      when '4' then '木'
         when '5' then '金'
           when '6' then '土'
          else 'undefined'
       end 
    ) AS WK 
    , 
    (
    case VALID_FLG
         when '1' then COUNT(USER_CODE)
         when '0' then 0
         end
    )
 AS TOTAL
FROM
    USERS 
WHERE
    -- VALID_FLG='1' aND 
    CONFIRMED_AT IS NOT NULL
    AND CONFIRMED_AT > "2022-07-31 23:59:59" AND CONFIRMED_AT < "2022-09-01 00:00:00"
GROUP BY date(CONFIRMED_AT)
ORDER BY
    REGIST_DATE ASC;