WITH RECURSIVE august(d) AS (
  VALUES('2022-08-01')
  UNION ALL
  SELECT date(d, '+1 day')
  FROM august
  WHERE d < '2022-08-31'
)
SELECT d AS REGIST_DATE, CASE strftime("%w", d) WHEN '0' THEN '日' WHEN '1' THEN '月' WHEN '2' THEN '火' WHEN '3' THEN '水' WHEN '4' THEN '木' WHEN '5' THEN '金' ELSE '土' END AS WK, COUNT(CONFIRMED_AT) AS TOTAL FROM august LEFT JOIN USERS ON d = date(CONFIRMED_AT) AND VALID_FLG='1' GROUP BY d ORDER BY 1;