WITH DATE_TBL AS (
SELECT
 '2022-08-01' AS CLDATE,
 STRFTIME('%w', '2022-08-01') AS WKNO,
 SUBSTR('日月火水木金土',STRFTIME('%w','2022-08-01') + 1,1) AS WEEK
 
UNION ALL
SELECT
 DATE (CLDATE,'+1 days'),
 STRFTIME('%w',DATE (CLDATE, '+1 days')),
 SUBSTR('日月火水木金土',STRFTIME('%w',DATE (CLDATE, '+1 days')) + 1,1
) 
FROM
 DATE_TBL
WHERE 
 CLDATE < '2022-08-31'
)

SELECT
 CLDATE AS REGIST_DATE,
 WEEK AS WK,
 COUNT(USER_CODE) AS TOTAL
FROM
 DATE_TBL 
 LEFT OUTER JOIN USERS
  ON DATE (USERS.CONFIRMED_AT) = DATE_TBL.CLDATE
  AND VALID_FLG = '1'
GROUP BY CLDATE
ORDER BY CLDATE ASC;