with DS as (select Date('2022-08-01', '+' || x || ' day') as DT, x%7 as YO
            from (select 0 as x
                  union all
                  select 1 as x
                  union all
                  select 2 as x
                  union all
                  select 3 as x
                  union all
                  select 4 as x
                  union all
                  select 5 as x
                  union all
                  select 6 as x
                  union all
                  select 7 as x
                  union all
                  select 8 as x
                  union all
                  select 9 as x
                  union all
                  select 10 as x
                  union all
                  select 11 as x
                  union all
                  select 12 as x
                  union all
                  select 13 as x
                  union all
                  select 14 as x
                  union all
                  select 15 as x
                  union all
                  select 16 as x
                  union all
                  select 17 as x
                  union all
                  select 18 as x
                  union all
                  select 19 as x
                  union all
                  select 20 as x
                  union all
                  select 21 as x
                  union all
                  select 22 as x
                  union all
                  select 23 as x
                  union all
                  select 24 as x
                  union all
                  select 25 as x
                  union all
                  select 26 as x
                  union all
                  select 27 as x
                  union all
                  select 28 as x
                  union all
                  select 29 as x
                  union all
                  select 30 as x))
select DS.DT as REGIST_DATE,
       substr('月火水木金土日', DS.YO+1,1) as WK,
       coalesce(count(users.USER_CODE), 0) as TOTAL
from DS
         left outer join USERS on DATE(USERS.CONFIRMED_AT) = DS.DT and users.VALID_FLG = '1'
group by DS.DT,DS.YO
order by 1;