WITH RECURSIVE dates(date) AS (
  VALUES('2022-08-01')
  UNION ALL
  SELECT date(date, '+1 day')
  FROM dates
  WHERE date < '2022-08-31'
)
SELECT 
  d.date AS REGIST_DATE,
  CASE strftime('%w',d.date)
   WHEN '0' THEN '日'
   WHEN '1' THEN '月'
   WHEN '2' THEN '火'
   WHEN '3' THEN '水'
   WHEN '4' THEN '木'
   WHEN '5' THEN '金'
   WHEN '6' THEN '土'
  END AS WK,
  COALESCE(COUNT(e.CONFIRMED_AT), 0) AS TOTAL
FROM 
  dates d
LEFT JOIN 
  USERS e ON d.date = DATE(e.CONFIRMED_AT)
  and VALID_FLG='1'
GROUP BY 
  d.date
ORDER BY 
  d.date;