WITH date_range AS (
  SELECT date('2022-08-01') AS date
  UNION ALL
  SELECT date(date, '+1 day')
  FROM date_range
  WHERE date < date('2022-08-31')
)
SELECT
  date_range.date AS REGIST_DATE,
  CASE strftime('%w', date_range.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,
  COUNT(USERS.USER_CODE) AS TOTAL
FROM
  date_range
LEFT JOIN
  USERS ON strftime('%Y-%m', USERS.CONFIRMED_AT) = '2022-08'
    AND strftime('%d', USERS.CONFIRMED_AT) = strftime('%d', date_range.date)
    AND USERS.VALID_FLG = '1'
GROUP BY
  date_range.date
ORDER BY
  date_range.date;