WITH BASE AS(
SELECT
	SUBSTR(A.CONFIRMED_AT,1,10) AS REGIST_DATE
,	COUNT(A.CONFIRMED_AT) AS TOTAL
FROM
	USERS A
WHERE
	A.VALID_FLG='1'
--AND	A.CONFIRMED_AT IS NOT NULL
AND	SUBSTR(A.CONFIRMED_AT,1,7) = '2022-08'
GROUP BY
	SUBSTR(A.CONFIRMED_AT,1,10)
),
MONTHLIST AS(
SELECT '2022-08-01' AS REGIST_DATE , '月' AS WK
UNION SELECT '2022-08-02','水'
UNION SELECT '2022-08-03','木'
UNION SELECT '2022-08-04','金'
UNION SELECT '2022-08-05','土'
UNION SELECT '2022-08-06','日'
UNION SELECT '2022-08-07','月'
UNION SELECT '2022-08-08','火'
UNION SELECT '2022-08-09','水'
UNION SELECT '2022-08-10','木'
UNION SELECT '2022-08-11','金'
UNION SELECT '2022-08-12','土'
UNION SELECT '2022-08-13','日'
UNION SELECT '2022-08-14','月'
UNION SELECT '2022-08-15','火'
UNION SELECT '2022-08-16','水'
UNION SELECT '2022-08-17','木'
UNION SELECT '2022-08-18','金'
UNION SELECT '2022-08-19','土'
UNION SELECT '2022-08-20','日'
UNION SELECT '2022-08-21','月'
UNION SELECT '2022-08-22','火'
UNION SELECT '2022-08-23','水'
UNION SELECT '2022-08-24','木'
UNION SELECT '2022-08-25','金'
UNION SELECT '2022-08-26','土'
UNION SELECT '2022-08-27','日'
UNION SELECT '2022-08-28','月'
UNION SELECT '2022-08-29','火'
UNION SELECT '2022-08-30','水'
UNION SELECT '2022-08-31','木'
)
SELECT
	A.REGIST_DATE
,	A.WK
,	COALESCE(B.TOTAL,0) AS TOTAL
FROM
	MONTHLIST A
LEFT OUTER JOIN
	BASE B
ON
	A.REGIST_DATE = B.REGIST_DATE
ORDER BY
	A.REGIST_DATE