WITH RECURSIVE calendar(calendar_date) AS (
  SELECT
    -- ここが始まりの日付
    DATE('2022-08-01') AS calendar_date
  UNION ALL
  -- UNION ALL 以下が再帰処理部分
  SELECT
    DATE(calendar_date, '+1 day') AS calendar_date
  FROM calendar
  WHERE
    -- ここが終わりの日付
    calendar_date < DATE('2022-08-31')
)

SELECT
  calendar_date AS REGIST_DATE,
  CASE 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 '金曜日'
       WHEN STRFTIME('%w', calendar_date) = '6' THEN '土曜日'
       WHEN STRFTIME('%w', calendar_date) = '0' THEN '日曜日'
       ELSE NULL END AS WK,
  CASE WHEN RE_DATE IS NULL THEN 0 ELSE COUNT(*) END AS TOTAL
FROM
  calendar AS c
  LEFT JOIN (SELECT USER_CODE, date(CONFIRMED_AT) AS RE_DATE, VALID_FLG FROM USERS WHERE CONFIRMED_AT IS NOT NULL AND VALID_FLG = 1) AS a ON c.calendar_date = a.RE_DATE
GROUP BY
  calendar_date
ORDER BY
  calendar_date