with dates as (
    select 0 as cnt
    union all
    select cnt+1 from dates
    where cnt < 30
),
aug as (
    select
        date(datetime('2022-08-01', '+' || CAST(cnt AS varchar) || ' days')) AS REGIST_DATE
    from
        dates
),
reg as (
    select
        date(CONFIRMED_AT) AS REGIST_DATE
        , count(*) TOTAL
    from
        USERS
    where
        CONFIRMED_AT >= '2022-08-01' AND CONFIRMED_AT < '2022-09-01'
        AND VALID_FLG='1'
    group by
        REGIST_DATE
)
select
    aug.REGIST_DATE
    , substring('日月火水木金土', strftime('%w', aug.REGIST_DATE)+1, 1) WK
    , CASE WHEN TOTAL IS NULL THEN 0 ELSE TOTAL END AS TOTAL
from aug
    left join reg
    on aug.REGIST_DATE = reg.REGIST_DATE
;