with dates as (select DATE('2022-08-01', '+' || SEQ.no || ' day') DT,
                      substr('月火水木金土日', SEQ.no % 7 + 1, 1) as     YO
               from (WITH SEQ AS (SELECT 0 as no UNION ALL SELECT no + 1 as no FROM SEQ WHERE no < 31)
                     select *
                     from seq) as SEQ)
select dates.DT as REGIST_DATE, dates.YO as WK, coalesce(count(users.user_code), 0) TOTAL
from dates
         left join users on dates.DT = DATE(USERS.CONFIRMED_AT)
        and VALID_FLG = '1'
group by dates.DT, dates.YO 
order by 1