WITH RECURSIVE dates(date) AS (
  VALUES('2022-08-01')
  UNION ALL
  SELECT date(date, '+1 day')
  FROM dates
  WHERE date < '2022-08-31'
),
aggr as
(
select 
  substr(CONFIRMED_AT, 1, 10) as REGIST_DATE_tmp
  ,count(distinct USER_CODE) as TOTAL
from USERS
where CONFIRMED_AT>='2022-08-01 00:00:00' and CONFIRMED_AT<'2022-09-01 00:00:00'
and VALID_FLG='1'
group by 1
)
select 
  date as REGIST_DATE
  ,case strftime('%w', date)
   when '0' then '日'
   when '1' then '月'
   when '2' then '火'
   when '3' then '水'
   when '4' then '木'
   when '5' then '金'
   when '6' then '土'
   else null end as WK
  
  ,case when TOTAL is null then 0 else TOTAL end as TOTAL
from dates a
left join aggr b
on a.date=b.REGIST_DATE_tmp

;