WITH RECURSIVE calendar(calendar_date) AS (
   SELECT
     DATE('2022-08-01') AS calendar_date
   UNION ALL
   SELECT
     DATE(calendar_date, '+1 day') AS calendar_date
   FROM calendar
   WHERE
     calendar_date < DATE('2022-08-31')
   )
SELECT
 A.calendar_date AS REGIST_DATE
 ,
 (CASE
   WHEN STRFTIME('%w', calendar_date) = '0' THEN '日'
   WHEN STRFTIME('%w', calendar_date) = '1' THEN '月'
   WHEN STRFTIME('%w', calendar_date) = '2' THEN '火'
   WHEN STRFTIME('%w', calendar_date) = '3' THEN '水'
   WHEN STRFTIME('%w', calendar_date) = '4' THEN '木'
   WHEN STRFTIME('%w', calendar_date) = '5' THEN '金'
   ELSE '土'
  END ) AS WK
 ,COUNT(B.USER_CODE) AS TOTAL
FROM
 calendar A
 LEFT OUTER JOIN (SELECT USER_CODE,CONFIRMED_AT FROM USERS WHERE VALID_FLG='1') B
 ON A.calendar_date = SUBSTR(B.CONFIRMED_AT,1,10)
GROUP BY
 A.calendar_date
ORDER BY 
 A.calendar_date ASC;