WITH 
dates(REGIST_DATE) AS (
    VALUES('2022-08-01')
    UNION ALL
    SELECT 
        date(REGIST_DATE, '+1 day')
    FROM
        dates
    WHERE 
        REGIST_DATE < '2022-08-31'
),
cnt as (
    select
        date(CONFIRMED_AT) as REGIST_DATE
        ,count() as TOTAL
    from
        USERS
    where
        VALID_FLG="1"
    group by
        REGIST_DATE
)
select
    d.REGIST_DATE
    ,substr("日月火水木金土",strftime("%w",d.REGIST_DATE)+1,1) as W
    ,coalesce(c.TOTAL,0) as TOTAL
from
    dates as d
    left join
    cnt as c
    on d.REGIST_DATE = c.REGIST_DATE
order by
    d.REGIST_DATE