select
    SUB.REGIST_DATE as REGIST_DATE,
    substr('日月火水木金土', strftime("%w", SUB.REGIST_DATE) + 1, 1) as "曜日",
    SUB.TOTAL
from
    (
        select
            STRFTIME('%Y-%m-%d', CONFIRMED_AT) as REGIST_DATE,
            count(
                case
                    when VALID_FLG = "1" then VALID_FLG
                    else NULL
                end
            ) as TOTAL
        from
            USERS
        where
            REGIST_DATE >= "2022-08-01"
            and REGIST_DATE <= "2022-08-31"
        group by
            REGIST_DATE
    ) as SUB;