with check_tbl as (
select
  substr(confirmed_at, 1, 10) as REGIST_DATE,
  strftime('%w', confirmed_at) as wk_num,
  count(USER_CODE) as TOTAL
from users
where substr(confirmed_at, 1, 7) = '2022-08'
and valid_flg = 1
group by REGIST_DATE, wk_num
)
select
  REGIST_DATE,
  case
    when wk_num = '0' then '日'
    when wk_num = '1' then '月'
    when wk_num = '2' then '火'
    when wk_num = '3' then '水'
    when wk_num = '4' then '木'
    when wk_num = '5' then '金'
    when wk_num = '6' then '土'
    else null
  end as WK,
  TOTAL
from
  check_tbl
order by REGIST_DATE ASC
;