select
    substr(CONFIRMED_AT,1,10) as REGIST_DATE
    ,CASE strftime('%w',substr(CONFIRMED_AT,1,10))
        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(*) as 'TOTAL'
from
    USERS
where
    VALID_FLG = '1'
    and substr(CONFIRMED_AT,6,2) = '08'
group by
    REGIST_DATE
order by
    REGIST_DATE asc
;