with recursive
generate_series(x) as (
 select 1
 union all
 select x+1 from generate_series limit 31
), check_tbl as (
select
  substr(confirmed_at, 1, 10) as REGIST_DATE,
  count(USER_CODE) as TOTAL
from users
where substr(confirmed_at, 1, 7) = '2022-08'
and valid_flg = 1
group by REGIST_DATE
), calendar as (
select 
  '2022-08-' || printf('%02d', x) as dt from generate_series
), combine as (
select
  t1.dt as REGIST_DATE,
  case
    when strftime('%w', REGIST_DATE) = '0' then '日'
    when strftime('%w', REGIST_DATE) = '1' then '月'
    when strftime('%w', REGIST_DATE) = '2' then '火'
    when strftime('%w', REGIST_DATE) = '3' then '水'
    when strftime('%w', REGIST_DATE) = '4' then '木'
    when strftime('%w', REGIST_DATE) = '5' then '金'
    when strftime('%w', REGIST_DATE) = '6' then '土'
    else null
  end as WK,
  ifnull(t2.TOTAL, 0) as TOTAL
from
  calendar t1
left outer join check_tbl t2
on t1.dt = t2.REGIST_DATE
)
select
  REGIST_DATE,
  case
    when strftime('%w', REGIST_DATE) = '0' then '日'
    when strftime('%w', REGIST_DATE) = '1' then '月'
    when strftime('%w', REGIST_DATE) = '2' then '火'
    when strftime('%w', REGIST_DATE) = '3' then '水'
    when strftime('%w', REGIST_DATE) = '4' then '木'
    when strftime('%w', REGIST_DATE) = '5' then '金'
    when strftime('%w', REGIST_DATE) = '6' then '土'
    else null
  end as WK,
  TOTAL
from combine
order by REGIST_DATE ASC
;