WITH dates(d) AS (
  VALUES('2022-08-01')
  UNION ALL
  SELECT date(d, '+1 day')
  FROM dates
  WHERE d < '2022-08-31'
)
SELECT
  d REGIST_DATE,
  substr('日月火水木金土', strftime("%w", d)+1, 1) WK,
  ifnull(TOTAL, 0) TOTAL
FROM
  dates
  LEFT JOIN (
    SELECT
      DATE(CONFIRMED_AT) REGIST_DATE,
      COUNT(DATE(CONFIRMED_AT)) TOTAL
    FROM USERS
    WHERE
      CONFIRMED_AT BETWEEN '2022-08-01' AND '2022-09-01'
      AND VALID_FLG = '1'
    GROUP BY DATE(CONFIRMED_AT)
  ) tmp
  ON dates.d = tmp.REGIST_DATE