with DATE_TBL as (
    select
        "2022-08-01" as DATE
    union
    all
    select
        date(DATE, "+1 day")
    from
        DATE_TBL
    where
        DATE < "2022-08-31"
),
SUB_TABLE as (
    select
        SUB.REGIST_DATE as REGIST_DATE,
        SUB.TOTAL as 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
)
select
    DATE_TBL.DATE as REGIST_DATE,
    substr(
        '日月火水木金土',
        strftime("%w", DATE_TBL.DATE) + 1,
        1
    ) as "WK",
    coalesce(SUB_TABLE.TOTAL, 0) as TOTAL
from
    DATE_TBL
    left outer join SUB_TABLE on STRFTIME('%Y-%m-%d', DATE_TBL.DATE) = SUB_TABLE.REGIST_DATE;