WITH RECURSIVE AUGUST AS (SELECT '2022-08-01' AS DATE, STRFTIME('%w', '2022-08-01') AS WEEK_DAY
UNION ALL SELECT DATE(DATE, '+1 days'), STRFTIME('%w', DATE(DATE, '+1 days'))
FROM AUGUST
WHERE DATE <= '2022-08-30')
SELECT DATE AS REGIST_DATE,
CASE WHEN WEEK_DAY = '0' THEN '日'
WHEN WEEK_DAY = '1' THEN '月'
WHEN WEEK_DAY = '2' THEN '火'
WHEN WEEK_DAY = '3' THEN '水'
WHEN WEEK_DAY = '4' THEN '木'
WHEN WEEK_DAY = '5' THEN '金'
WHEN WEEK_DAY = '6' THEN '土' END AS WK,
COUNT(USER_CODE) AS TOTAL
FROM AUGUST A LEFT JOIN USERS U
ON DATE = SUBSTRING(U.CONFIRMED_AT, 1, 10)
AND VALID_FLG = '1'
GROUP BY REGIST_DATE