-- 日付テーブルを作成
WITH RECURSIVE tbl_date(date) AS (
  SELECT
    -- 開始日付
    DATE('2022-08-01') AS date
  UNION ALL
    SELECT DATE(date, '+1 day') AS date
    FROM tbl_date
    WHERE
      -- 終了日付
      date < DATE('2022-09-01')
)
-- ここから実際のSELECT文
SELECT
    tbl_date.date AS REGIST_DATE
  , CASE strftime('%w', tbl_date.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(VALID_FLG = '1' OR NULL) AS TOTAL
FROM tbl_date
LEFT JOIN USERS
  ON tbl_date.date = DATE(USERS.CONFIRMED_AT)
WHERE tbl_date.date LIKE '2022-08-%'
GROUP BY tbl_date.date
ORDER BY 
  REGIST_DATE ASC