WITH date_range AS (
  SELECT date('2022-08-01') AS date
  UNION ALL
  SELECT date(date, '+1 day')
  FROM date_range
  WHERE date < '2022-08-31'
)
select  strftime('%Y-%m-%d', CONFIRMED_AT) as REGIST_DATE
       ,case strftime('%w', CONFIRMED_AT)
             when '0' then '日'
             when '1' then '月'
             when '2' then '火'
             when '3' then '水'
             when '4' then '木'
             when '5' then '金'
             when '6' then '土'
        end as WK
       ,count(*) as TOTAL
from  USERS,
      date_range
where REGIST_DATE between  '2022-08-01' and  '2022-08-31'
and   VALID_FLG = '1'
and   date_range.date = REGIST_DATE
group by REGIST_DATE
order by REGIST_DATE