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 '土' 
        END AS WK
    , count(*) AS TOTAL 
FROM
    users 
WHERE
    valid_flg = '1' 
GROUP BY
    DATE(confirmed_at)
ORDER BY
    REGIST_DATE ASC