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;