WITH RECURSIVE calendar(calendar_date) AS (
    SELECT DATE('2022-08-01') AS calendar_date --start
    UNION ALL
    SELECT DATE(calendar_date, '+1 day') AS calendar_date
    FROM calendar
    WHERE calendar_date < DATE('2022-08-31') --end
)
SELECT 
  calendar_date as REGIST_DATE
  ,substr('日月火水木金土', strftime("%w", calendar_date)+1, 1) as WK
  ,ifnull(TOTAL, 0) as TOTAL
FROM calendar c 
left join 
 ( select 
    date(confirmed_at) as reg_date
  --   ,日付に対応した曜日 as WK
    ,count(0) as TOTAL
  from users
  
  where 
    valid_flg = '1'
  -- 2022-08に登録された会員
    and confirmed_at >= '2022-08-01 00:00:00'
    and confirmed_at < '2022-09-01 00:00:00'
  group by 1) as b
  on c.calendar_date = b.reg_date
order by REGIST_DATE
;