with t1 as (
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', '/', '-') ))
SELECT strftime('%Y-%m-%d', x) as REGIST_DATE, 0 AS DATE FROM cnt
), t2 as(
-- select * from t1
-- CONFIRMED_AT を日付
select SUBSTRING(CONFIRMED_AT,1,10) as REGIST_DATE, count(1) as TOTAL from USERS
where VALID_FLG = '1'
group by 1
)
select 
    t1.REGIST_DATE,
    CASE strftime('%w', t1.REGIST_DATE)
        when "1" then "月"
        when "2" then "火"
        when "3" then "水"
        when "4" then "木"
        when "5" then "金"
        when "6" then "土"
        when "0" then "日"
    END AS WK,
    coalesce(t2.TOTAL, 0) AS TOTAL
from t1
left join t2
on t1.REGIST_DATE = t2.REGIST_DATE
order by 1