WITH CAL AS(
SELECT '2022-08-01' AS CALDATE UNION ALL
SELECT '2022-08-02' AS CALDATE UNION ALL
SELECT '2022-08-03' AS CALDATE UNION ALL
SELECT '2022-08-04' AS CALDATE UNION ALL
SELECT '2022-08-05' AS CALDATE UNION ALL
SELECT '2022-08-06' AS CALDATE UNION ALL
SELECT '2022-08-07' AS CALDATE UNION ALL
SELECT '2022-08-08' AS CALDATE UNION ALL
SELECT '2022-08-09' AS CALDATE UNION ALL
SELECT '2022-08-10' AS CALDATE UNION ALL
SELECT '2022-08-11' AS CALDATE UNION ALL
SELECT '2022-08-12' AS CALDATE UNION ALL
SELECT '2022-08-13' AS CALDATE UNION ALL
SELECT '2022-08-14' AS CALDATE UNION ALL
SELECT '2022-08-15' AS CALDATE UNION ALL
SELECT '2022-08-16' AS CALDATE UNION ALL
SELECT '2022-08-17' AS CALDATE UNION ALL
SELECT '2022-08-18' AS CALDATE UNION ALL
SELECT '2022-08-19' AS CALDATE UNION ALL
SELECT '2022-08-20' AS CALDATE UNION ALL
SELECT '2022-08-21' AS CALDATE UNION ALL
SELECT '2022-08-22' AS CALDATE UNION ALL
SELECT '2022-08-23' AS CALDATE UNION ALL
SELECT '2022-08-24' AS CALDATE UNION ALL
SELECT '2022-08-25' AS CALDATE UNION ALL
SELECT '2022-08-26' AS CALDATE UNION ALL
SELECT '2022-08-27' AS CALDATE UNION ALL
SELECT '2022-08-28' AS CALDATE UNION ALL
SELECT '2022-08-29' AS CALDATE UNION ALL
SELECT '2022-08-30' AS CALDATE UNION ALL
SELECT '2022-08-31' AS CALDATE
)

SELECT 
	C.CALDATE AS REGIST_DATE,
	substr('日月火水木金土', strftime("%w",C.CALDATE)+1, 1) as WK,
    sum(CASE WHEN VALID_FLG is NULL THEN 0 ELSE VALID_FLG END) AS TOTAL
FROM
    CAL C
LEFT OUTER JOIN
	USERS U
ON
    C.CALDATE = date(U.CONFIRMED_AT)
    AND
	U.VALID_FLG='1'
	AND
	strftime('%Y', U.CONFIRMED_AT) = '2022'
	AND
	strftime('%m', U.CONFIRMED_AT) = '08'
GROUP BY
    C.CALDATE
ORDER BY
    C.CALDATE