create temporary table regdates (regdate string);
insert into regdates
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
  regdate as REGIST_DATE,
  case strftime('%w', regdate)
    when '0' then '日'
    when '1' then '月'
    when '2' then '火'
    when '3' then '水'
    when '4' then '木'
    when '5' then '金'
    when '6' then '土'
  end as WK,
  sum(u.valid_flg is not null) as TOTAL
from
  regdates r
left outer join
  users u on r.regdate = date(u.confirmed_at) and u.valid_flg='1'
group by
  r.regdate
order by r.regdate
;