WITH RECURSIVE
  cnt(x) AS (VALUES(julianday(replace('2022/08/01', '/', '-') ))
  UNION ALL
   SELECT x+1 FROM cnt WHERE x < julianday(replace('2022/08/31', '/', '-') ))
, base as (
select
  strftime('%Y-%m-%d', CONFIRMED_AT) as REGIST_DATE
  ,count(*) as TOTAL
from
  USERS as main
where
  strftime('%Y-%m', CREATED_AT) = '2022-08'
  and VALID_FLG='1'
 group by
   strftime('%Y-%m-%d', CONFIRMED_AT)
having
   strftime('%Y-%m-%d', CONFIRMED_AT) is not null
)
SELECT
  strftime('%Y-%m-%d', cnt.x) as REGIST_DATE
  ,substr('日月火水木金土', strftime("%w", strftime('%Y-%m-%d', cnt.x))+1, 1) as WK
  ,ifnull(base.TOTAL, 0) as TOTAL
FROM cnt
left join base on
  strftime('%Y-%m-%d', cnt.x) = base.REGIST_DATE
order by
  REGIST_DATE asc