WITH RECURSIVE day(i) AS (
  SELECT 1
  UNION
  SELECT i + 1 FROM day WHERE i + 1 <= 31
), day_str AS (
    SELECT '2022-08-' || printf('%02d', i) AS d FROM day
)


SELECT
d AS REGIST_DATE,
CASE
    WHEN STRFTIME("%w", d) = '0' THEN '日'
    WHEN STRFTIME("%w", d) = '1' THEN '月'
    WHEN STRFTIME("%w", d) = '2' THEN '火'
    WHEN STRFTIME("%w", d) = '3' THEN '水'
    WHEN STRFTIME("%w", d) = '4' THEN '木'
    WHEN STRFTIME("%w", d) = '5' THEN '金'
    WHEN STRFTIME("%w", d) = '6' THEN '土'
END AS WK,
COUNT(CONFIRMED_AT) AS TOTAL
FROM day_str
LEFT OUTER JOIN (SELECT * FROM USERS WHERE VALID_FLG='1' AND CONFIRMED_AT BETWEEN '2022-08-01' AND '2022-08-31')
ON d = DATE(CONFIRMED_AT)
GROUP BY 1, 2
ORDER BY REGIST_DATE