with T1 as (
    select '2022-08-01' as REGIST_DATE 
    union all
    select date(REGIST_DATE, '+1 days')
    from  T1
    where REGIST_DATE < '2022-08-31' 
    )
, T2 as (
    select date(CONFIRMED_AT) as REGIST_DATE, count(USER_CODE) as TOTAL
    from USERS
    where  CONFIRMED_AT is not NULL
        and date(CONFIRMED_AT) between '2022-08-01' and '2022-08-31'
        and VALID_FLG = '1'
    group by date(CONFIRMED_AT)
)
select T1.REGIST_DATE,
     substr('日月火水木金土', STRFTIME('%w', T1.REGIST_DATE) + 1, 1) as WK,
     ifnull ( TOTAL , 0 ) as TOTAL
    from T1
    left outer join T2 on T2.REGIST_DATE = T1.REGIST_DATE
    order by T1.REGIST_DATE