with tmp(REGIST_DATE,WK) as(
values('2022-08-01' , '月'),
      ('2022-08-02' , '火'),
      ('2022-08-03' , '水'),
      ('2022-08-04' , '木'),
      ('2022-08-05' , '金'),
      ('2022-08-06' , '土'),
      ('2022-08-07' , '日'),
      ('2022-08-08' , '月'),
      ('2022-08-09' , '火'),
      ('2022-08-10' , '水'),
      ('2022-08-11' , '木'),
      ('2022-08-12' , '金'),
      ('2022-08-13' , '土'),
      ('2022-08-14' , '日'),
      ('2022-08-15' , '月'),
      ('2022-08-16' , '火'),
      ('2022-08-17' , '水'),
      ('2022-08-18' , '木'),
      ('2022-08-19' , '金'),
      ('2022-08-20' , '土'),
      ('2022-08-21' , '日'),
      ('2022-08-22' , '月'),
      ('2022-08-23' , '火'),
      ('2022-08-24' , '水'),
      ('2022-08-25' , '木'),
      ('2022-08-26' , '金'),
      ('2022-08-27' , '土'),
      ('2022-08-28' , '日'),
      ('2022-08-29' , '月'),
      ('2022-08-30' , '火'),
      ('2022-08-31' , '水'))
select REGIST_DATE,WK,
(select count(*)
   from USERS b
  where b.VALID_FLG = '1'
    and substr(b.CONFIRMED_AT,1,10) = a.REGIST_DATE) as TOTAL
from tmp a
order by REGIST_DATE